package com.xgry.bpm.api.base.sqldata.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.xgry.bpm.api.base.createtable.entity.CreateTable;
import com.xgry.bpm.api.base.createtable.mapper.CreateTableMapper;
import com.xgry.bpm.api.base.dataPermissions.entity.DataPermissions;
import com.xgry.bpm.api.base.dataPermissions.mapper.DataPermissionsMapper;
import com.xgry.bpm.api.base.dbinfo.mapper.DbinfoMapper;
import com.xgry.bpm.api.base.formlist.entity.FormList;
import com.xgry.bpm.api.base.formlist.mapper.FormListMapper;
import com.xgry.bpm.api.base.formltem.entity.FormLtem;
import com.xgry.bpm.api.base.formltem.mapper.FormLtemMapper;
import com.xgry.bpm.api.base.jurisdiction.entity.UserinfoJurisdiction;
import com.xgry.bpm.api.base.jurisdiction.mapper.UserinfoJurisdictionMapper;
import com.xgry.bpm.api.base.paymentorderpage.entity.PaymentOrderPage;
import com.xgry.bpm.api.base.paymentorderpagetablebody.entity.PayMentOrderPageTableBody;
import com.xgry.bpm.api.base.sqldata.service.SqlData;
import com.xgry.bpm.api.base.tableclass.entity.TableClass;
import com.xgry.bpm.api.base.tableclass.mapper.TableClassMapper;
import com.xgry.bpm.api.base.userInfo.entity.UserInfo;
import com.xgry.bpm.common.entity.TableField;
import com.xgry.bpm.common.enums.SystemEnum;
import com.xgry.bpm.common.utils.MD5Util;
import com.xgry.bpm.config.datasouce.oldsource.DataDBServer;
import com.xgry.bpm.config.datasouce.oldsource.MSFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.math.BigDecimal;
import java.util.*;
import java.util.Map.Entry;

/**
 * @author Mr.Xing
 * @version 创建时间：2018年9月20日 下午3:14:18
 * JDBC的CRUD sql拼接
 */
@Transactional
@Repository("SqlData")
public class SqlDataSourceSQL implements SqlData {

    @Autowired
    private CreateTableMapper createTableDao;
    @Autowired
    private DataDBServer dbServer;
    @Autowired
    private MSFactory msFactory;
    @Autowired
    private TableClassMapper tableClassDao;
    @Autowired
    private FormListMapper formListDao;
    @Autowired
    private FormLtemMapper formItemsDao;
    @Autowired
    private DbinfoMapper dbInfoDao;
    @Autowired
    private UserinfoJurisdictionMapper userinfo_jurisdictionDao;
    @Autowired
    private DataPermissionsMapper dataPermissionsDao;

    @SuppressWarnings("all")
    public void newCreateTable(JSONObject json) throws Exception {
        StringBuffer sb = new StringBuffer();
        String tabname = json.getJSONObject("data").getString("cTableName");
        JdbcTemplate jdbc = dbServer.getJdbcTemplate(json.getJSONObject("data").getInteger("dbinfo_id"));

        JdbcTemplate msJdbc = msFactory.getJdbcTemplate();

        List<Map<String, Object>> maps = msJdbc.queryForList("select * from DefaultField");

        sb.append("CREATE TABLE " + tabname);
        sb.append(" ( " + tabname + "_id int IDENTITY(1,1) NOT NULL,");
//		JSONArray detail=json.getJSONArray("detail");
        JSONArray cols = JSON.parseArray(JSON.toJSONString(maps));
        for (int i = 0; i < cols.size(); i++) {
            JSONObject col = cols.getJSONObject(i);
            String colname = col.getString("fieldName");
            if ("parent_level".equals(colname)) {
                sb.append(" " + colname + " ");
            } else {
                sb.append(tabname + "_" + colname);
            }
            String datatype = col.getString("fieldType");
            sb.append(" " + datatype + " ");
            sb.append(" NULL");
            sb.append(",");
        }
        sb.deleteCharAt(sb.length() - 1);
        String uuid = UUID.randomUUID().toString();
        sb.append(") ALTER TABLE " + tabname + " ADD CONSTRAINT [" + uuid + "] PRIMARY KEY CLUSTERED (" + tabname + "_id)" +
                "WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) " +
                "ON [PRIMARY]");
        String createSql = sb.toString();
        jdbc.update(createSql);
    }

    @SuppressWarnings("all")
    public void createStable(JSONObject json) throws Exception {
        StringBuffer sb = new StringBuffer();
        String tabname = json.getJSONObject("data").getString("cTableName");
        JdbcTemplate jdbc = dbServer.getJdbcTemplate(json.getJSONObject("data").getInteger("dbinfo_id"));

        JdbcTemplate msJdbc = msFactory.getJdbcTemplate();

        List<Map<String, Object>> maps = msJdbc.queryForList("select * from DefaultField");

        sb.append("CREATE TABLE " + tabname);
        sb.append(" ( " + tabname + "_id int IDENTITY(1,1) NOT NULL," + json.getJSONObject("data").getString("mTableName") + "_id int not null,");
//		JSONArray detail=json.getJSONArray("detail");
        JSONArray cols = JSON.parseArray(JSON.toJSONString(maps));
        for (int i = 0; i < cols.size(); i++) {
            JSONObject col = cols.getJSONObject(i);
            String colname = col.getString("fieldName");
            if ("parent_level".equals(colname)) {
                sb.append(" " + colname + " ");
            } else {
                sb.append(tabname + "_" + colname);
            }
            String datatype = col.getString("fieldType");
            sb.append(" " + datatype + " ");
            sb.append(" NULL");
            sb.append(",");
        }
        sb.deleteCharAt(sb.length() - 1);
        String uuid = UUID.randomUUID().toString();
        sb.append(") ALTER TABLE " + tabname + " ADD CONSTRAINT [" + uuid + "] PRIMARY KEY CLUSTERED (" + tabname + "_id)" +
                "WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) " +
                "ON [PRIMARY]");
        String createSql = sb.toString();
        jdbc.update(createSql);
    }

    @SuppressWarnings("all")
    public void newCreateField(JSONObject jsonObject) throws Exception {
        //	System.out.println("newCreateField" + jsonObject);
        String tableName = jsonObject.getString("tableName");
        JdbcTemplate msJdbc = msFactory.getJdbcTemplate();
        List<Map<String, Object>> maps = msJdbc.queryForList("select * from table_class where tableClassName = '" + tableName + "'");
        JSONArray cols = JSON.parseArray(JSON.toJSONString(maps));
        JSONObject jdbcJSON = cols.getJSONObject(0);
        JdbcTemplate jdbc = dbServer.getJdbcTemplate(jdbcJSON.getInteger("dbInfoId"));

        StringBuffer sb = new StringBuffer();
        sb.append("alter table " + tableName + " add " + jsonObject.getString("field") + " ");
        sb.append(jsonObject.getString("type"));
        if (jsonObject.getString("notNull") == "false") {
            if (jsonObject.getString("type") == "int") {
                sb.append("not null default 0");
            } else {
                sb.append("not null default ''");
            }
        }
//		System.out.println("sb.toString():" + sb.toString());
        jdbc.update(sb.toString());
    }

    @SuppressWarnings("all")
    @Override
    public void delete(JSONArray jsonArray) throws Exception {
        //	System.out.println("jsonArray:" + jsonArray);
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObject = jsonArray.getJSONObject(i);
            String tableName = jsonObject.getString("cTableName");
            JdbcTemplate msJdbc = msFactory.getJdbcTemplate();
            List<Map<String, Object>> maps = msJdbc.queryForList("select * from table_class where tableClassName = '" + tableName + "'");
            JSONArray cols = JSON.parseArray(JSON.toJSONString(maps));
            JSONObject jdbcJSON = cols.getJSONObject(0);
            JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(jdbcJSON.getInteger("dbInfoId"));
            StringBuffer stringBuffer = new StringBuffer();
            List<Map<String, Object>> sTableList = msJdbc.queryForList("select * from table_class where mTableId=" + jdbcJSON.getInteger("tableClassId"));
            stringBuffer.append("delete from " + tableName + " where " + tableName + "_id=" + jsonObject.getInteger("id"));
            if (sTableList.size() > 0) {
                JSONArray sTableJSONArray = JSON.parseArray(JSON.toJSONString(sTableList));
                for (int a = 0; a < sTableJSONArray.size(); a++) {
                    JSONObject sTableJSON = sTableJSONArray.getJSONObject(a);
                    jdbcTemplate.update("delete from " + sTableJSON.getString("tableClassName")
                            + " where " + tableName + "_id=" + jsonObject.getInteger("id"));
                }
                jdbcTemplate.update(stringBuffer.toString());
            } else {
                jdbcTemplate.update(stringBuffer.toString());
            }
        }
    }

    /**
     * 表单的汇总显示
     *
     * @param formListId 表单id
     * @return jsonArray
     * @throws Exception
     */
    @SuppressWarnings("all")
    public JSONArray findByAll(int formListId) throws Exception {
        JSONArray jsonArray = new JSONArray();
        //获取系统jdbc链接对象
        JdbcTemplate jdbcTemplate = msFactory.getJdbcTemplate();
        StringBuffer stringBuffer = new StringBuffer();
        //使用sql查询表单
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList("select * from form_list where formListId=" + formListId);
        JSONArray formList = JSONArray.parseArray(JSON.toJSONString(mapList));
        JSONObject jsonObject = formList.getJSONObject(0);
        String mTable = jsonObject.getString("cTableName");
        //	System.out.println("mTable:" + mTable);
        //获取主表信息
        List<Map<String, Object>> tableList = jdbcTemplate.queryForList("select * from table_class where tableClassName='" + mTable + "'");
        JSONArray cols = JSON.parseArray(JSON.toJSONString(tableList));
        JSONObject jdbcJSON = cols.getJSONObject(0);
        //获取动态数据库连接对象，操作数据库
        JdbcTemplate dbServerJdbcTemplate = dbServer.getJdbcTemplate(jdbcJSON.getInteger("dbInfoId"));
        int mTableId = jdbcJSON.getInteger("tableClassId");
        //查询主表明细信息
        List<CreateTable> createTables = createTableDao.selectList(new QueryWrapper<CreateTable>() {{
            this.eq("tableName", mTable);
        }});
        //获取主表信息
        TableClass tableClass = tableClassDao.selectOne(new QueryWrapper<TableClass>() {{
            this.eq("tableClassName", mTable);
        }});

        stringBuffer.append("select ");
        stringBuffer.append(mTable + "." + mTable + "_id as " + mTable + "_id,");
        for (int b = 0; b < createTables.size(); b++) {
            CreateTable createTable = createTables.get(b);
            FormLtem formltems = formItemsDao.selectOne(new QueryWrapper<FormLtem>() {{
                this.eq("cTableField", createTable.getField());
                this.eq("cTableName", mTable);
            }});
            if ("datetime".equals(createTable.getType())) {
                stringBuffer.append("( select convert(varchar(100)," + createTable.getField() + ",120)) as " + createTable.getField() + ",");
            } else if (formltems != null) {
                if (!"{}".equals(formltems.getRef())) {
                    JSONObject refJSON = JSON.parseObject(formltems.getRef());
                    JSONObject ref = JSON.parseObject(refJSON.getString("ref"));
                    stringBuffer.append("(select " + ref.getString("cTableField") + " from " + ref.getString("cTableName") + " where "
                            + ref.getString("cTableName") + "_id=" + mTable + "." + formltems.getCTableField() + " ) as " + createTable.getField() + ",");
                } else {
                    stringBuffer.append(mTable + "." + createTable.getField() + " as " + createTable.getField() + ",");
                }
            } else if (createTable.getField() == "parent_level") {
                stringBuffer.append("(select  " + mTable + "_name from " + mTable + " where " + mTable + "_id =" + mTable + ")");
            }
        }
        stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
        stringBuffer.append(" from " + mTable);
        List<Map<String, Object>> formDataList = dbServerJdbcTemplate.queryForList(stringBuffer.toString());
        JSONArray fromDataList = JSONArray.parseArray(JSON.toJSONString(formDataList));
        return fromDataList;
    }


    private void deleteStable(String mTableName, int mTableId, JdbcTemplate jdbcTemplate) {
        TableClass tableClass = tableClassDao.selectOne(new QueryWrapper<TableClass>() {{
            this.eq("tableClassName", mTableName);
        }});
        List<TableClass> tableClasses = tableClassDao.selectList(new QueryWrapper<TableClass>() {{
            this.eq("mTableId", tableClass.getTableClassId());
        }});
        StringBuffer stringBuffer = new StringBuffer();
        if (tableClasses.size() > 0) {
            for (int i = 0; i < tableClasses.size(); i++) {
                String tableName = tableClasses.get(i).getTableClassName();
                stringBuffer.append("delete from " + tableName
                        + " where " + mTableName + "_id=" + mTableId + ";");
            }
            jdbcTemplate.update(stringBuffer.toString());
        }
    }

    /**
     * 业务关联，非强制关联
     *
     * @param jsonObject
     * @throws Exception
     */
    @SuppressWarnings("all")
    public void createKey(JSONObject jsonObject) throws Exception {
        String tableName = jsonObject.getString("tableName");
        JdbcTemplate msJdbc = msFactory.getJdbcTemplate();
        List<Map<String, Object>> maps = msJdbc.queryForList("select * from table_class where tableClassName = '" + tableName + "'");
        JSONArray cols = JSON.parseArray(JSON.toJSONString(maps));
        JSONObject jdbcJSON = cols.getJSONObject(0);
        JdbcTemplate jdbc = dbServer.getJdbcTemplate(jdbcJSON.getInteger("dbInfoId"));

        StringBuffer sb = new StringBuffer();
        sb.append("alter table " + tableName + " add " + jsonObject.getString("field"));
        sb.append(jsonObject.getString("  type"));
        sb.append("not null default 0");
        jdbc.update(sb.toString());
    }

    @SuppressWarnings("all")
    public JSONObject insertData(JSONArray jsonArray) throws Exception {
        JSONObject reJSON = new JSONObject();
//		try {

        JSONObject jsonObject = jsonArray.getJSONObject(0);

        String tableName = jsonObject.getString("cTableName");
        JdbcTemplate msJdbc = msFactory.getJdbcTemplate();
        List<Map<String, Object>> maps = msJdbc.queryForList("select * from table_class where tableClassName = '" + tableName + "'");
        JSONArray cols = JSON.parseArray(JSON.toJSONString(maps));
        JSONObject jdbcJSON = cols.getJSONObject(0);
        JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(jdbcJSON.getInteger("dbInfoId"));

        int kid = 0;

        for (int a = 0; a < jsonArray.size(); a++) {
            StringBuffer stringBuffer = new StringBuffer();
            JSONObject setJSON = jsonArray.getJSONObject(a);
            setJSON.remove("mTableId");
            setJSON.remove("id");

            List<Map<String, Object>> mapList = msJdbc.queryForList("select * from create_table where tableName='" + setJSON.getString("cTableName") + "'");
            JSONArray fieldJSONArray = JSON.parseArray(JSON.toJSONString(mapList));
            JSONObject keyAndValue = new JSONObject(true);
            JSONObject webKeyAndValue = setJSON.getJSONObject("obj");
            List list = new ArrayList();
            if (webKeyAndValue.containsKey("ids")) {
                String ids[] = webKeyAndValue.getString("ids").replace("[", "").replace("]", "").replace("\"", "").replace("\"", "").split(",");
                for (int i = 0; i < ids.length; i++) {
                    list.add(ids[i]);
                }
            }
            webKeyAndValue.remove("ids");
            webKeyAndValue.remove("mTableId");
            webKeyAndValue.remove("_id");
            webKeyAndValue.remove("id");
            webKeyAndValue.remove("_uid");
            webKeyAndValue.remove("Provinces_xgry_0001");
            webKeyAndValue.remove("" + tableName + "_id");
            JSONObject fieldJson = new JSONObject(true);
            if (setJSON.getString("tableType") == "main" || "main".equals(setJSON.getString("tableType"))) {
//                	webKeyAndValue.remove("id");
//
//                	fieldJson.remove("mTableId");
//                	keyAndValue.remove("mTableId");

                stringBuffer.append("insert into " + setJSON.getString("cTableName") + "(");

                for (int b = 0; b < fieldJSONArray.size(); b++) {
                    keyAndValue.put(fieldJSONArray.getJSONObject(b).getString("field"), webKeyAndValue.getString(fieldJSONArray.getJSONObject(b).getString("field")));
                    fieldJson.put(fieldJSONArray.getJSONObject(b).getString("field"), fieldJSONArray.getJSONObject(b).getString("type"));

                }

                for (Entry<String, Object> entry : webKeyAndValue.entrySet()) {
                    stringBuffer.append(entry.getKey() + ",");
                }
                stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
                stringBuffer.append(") values  (");
                ArrayList arr = new ArrayList();
                for (Entry<String, Object> entry : webKeyAndValue.entrySet()) {
                    String string = fieldJson.getString(entry.getKey() + " ");
                    if ("datetime".equals(fieldJson.getString(entry.getKey()))) {
                        if (entry.getValue() != null && !"".equals(entry.getValue())) {
                            stringBuffer.append("'" + entry.getValue() + "',");
                        } else {
                            stringBuffer.append("null,");
                        }
                    } else if ("varchar(8000)".equals(fieldJson.getString(entry.getKey())) && entry.getValue() != null) {
                        //stringBuffer.append("'" + entry.getValue() + "',");
                        stringBuffer.append("'" + entry.getValue() + "',");
                    } else if ("text".equals(fieldJson.getString(entry.getKey()))) {
                        //			System.out.println("entry.getValue():" + entry.getValue());
                        if (entry.getValue() != null && !"".equals(entry.getValue())) {
                            stringBuffer.append("'" + entry.getValue() + "',");
                        } else {
                            stringBuffer.append("null,");
                        }
                    } else {
                        stringBuffer.append(entry.getValue() + ",");
                    }

                }
                stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
                stringBuffer.append(");SET NOCOUNT ON SELECT SCOPE_IDENTITY() as " + setJSON.getString("cTableName") + "_id");
                //	System.out.println("str22222222222222" + stringBuffer);
                List<Map<String, Object>> maps1 = jdbcTemplate.queryForList(stringBuffer.toString());
                JSONArray ma = JSONArray.parseArray(JSON.toJSONString(maps1));
                JSONObject mTableId = ma.getJSONObject(0);
                kid = mTableId.getInteger(setJSON.getString("cTableName") + "_id");

            } else {
                setJSON.remove("_uid");
                setJSON.remove("_id");
                setJSON.remove("mTableId");
                stringBuffer.append("insert into " + setJSON.getString("cTableName") + "(");
                for (int b = 0; b < fieldJSONArray.size(); b++) {
                    keyAndValue.put(fieldJSONArray.getJSONObject(b).getString("field"), webKeyAndValue.getString(fieldJSONArray.getJSONObject(b).getString("field")));
                    fieldJson.put(fieldJSONArray.getJSONObject(b).getString("field"), fieldJSONArray.getJSONObject(b).getString("type"));
                }
                TableClass tableClass = tableClassDao.selectOne(new QueryWrapper<TableClass>() {{
                    this.eq("tableClassName", tableName);
                }});
                webKeyAndValue.put(tableClass.getTableClassName() + "_id", kid);
                for (Entry<String, Object> entry : webKeyAndValue.entrySet()) {
                    stringBuffer.append(entry.getKey() + ",");
                }
                stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
                stringBuffer.append(") values  (");
                ArrayList arr = new ArrayList();
                for (Entry<String, Object> entry : webKeyAndValue.entrySet()) {
                    if ("datetime".equals(fieldJson.getString(entry.getKey()))) {
                        if (entry.getValue() != null && !"".equals(entry.getValue())) {
                            stringBuffer.append("'" + entry.getValue() + "',");
                        } else {
                            stringBuffer.append("null,");
                        }
                    } else if ("varchar(8000)".equals(fieldJson.getString(entry.getKey())) && entry.getValue() != null) {
                        stringBuffer.append("'" + entry.getValue() + "',");
                    } else if ("text".equals(fieldJson.getString(entry.getKey()))) {
                        if (entry.getValue() != null && !"".equals(entry.getValue())) {
                            stringBuffer.append("'" + entry.getValue() + "',");
                        } else {
                            stringBuffer.append("null,");
                        }
                    } else {
                        stringBuffer.append(entry.getValue() + ",");
                    }
                }
                stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
                stringBuffer.append(")");
                jdbcTemplate.update(stringBuffer.toString());

            }

            if (list.size() > 0) {
                for (int i = 0; i < list.size(); i++) {
                    msJdbc.update("update Upload set joinName='" + tableName + "' , joinid=" + kid + " where upload_id=" + list.get(i) + "");
                }
            }
            //	System.out.println("-----------------------------------" + stringBuffer.toString());
        }

        reJSON.put("id", kid);

//		System.out.println("reJSON:" + reJSON);
//		} catch (Exception e) {
//			throw new JdbcException(e.getMessage());
//		}

        return reJSON;
    }

    public JSONObject updateData(JSONArray jsonArray) throws Exception {
        JSONObject reJson = new JSONObject();
//		System.out.println("svn"+jsonArray);
        JSONObject jsonObject = jsonArray.getJSONObject(0);
        String mTableName = jsonObject.getString("cTableName");
        JSONObject object = jsonObject.getJSONObject("obj");
        int mTableId = object.getInteger("id");
        JdbcTemplate jdbcTemplates = msFactory.getJdbcTemplate();
        List list = new ArrayList();
        if (object.containsKey("ids")) {
            String ids[] = object.getString("ids").replace("[", "").replace("]", "").replace("\"", "").replace("\"", "").split(",");
            for (int i = 0; i < ids.length; i++) {
                list.add(ids[i]);
            }
        }
        reJson.put("id", mTableId);
        object.remove("id");
        object.remove("ids");
        TableClass tableClass = tableClassDao.selectOne(new QueryWrapper<TableClass>() {{
            this.eq("tableClassName", mTableName);
        }});
        JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(tableClass.getDbInfoId());
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("UPDATE " + mTableName + " SET ");
        for (Entry<String, Object> entry : object.entrySet()) {
            CreateTable createTables = createTableDao.selectOne(new QueryWrapper<CreateTable>() {{
                this.eq("tableName", tableClass.getTableClassName());
                this.eq("field", entry.getKey());
            }});
            if ("varchar(8000)".equals(createTables.getType())) {
                stringBuffer.append(entry.getKey() + " = " + "'" + entry.getValue() + "',");
            }
            if ("numeric(18,5)".equals(createTables.getType())) {
                stringBuffer.append(entry.getKey() + " = " + "" + entry.getValue() + ",");
            }
            if ("datetime".equals(createTables.getType())) {
                if (entry.getValue() != null && !"".equals(entry.getValue())) {
                    stringBuffer.append(entry.getKey() + " = " + "'" + entry.getValue() + "',");
                } else {
                    stringBuffer.append(entry.getKey() + " = " + "null,");
                }
            }

        }
        stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
        stringBuffer.append(" where " + mTableName + "_id=" + mTableId);
        jdbcTemplate.update(stringBuffer.toString());
        if (list.size() > 0) {
            for (int i = 0; i < list.size(); i++) {
                jdbcTemplates.update("update Upload set joinName='" + mTableName + "' , joinid=" + mTableId + " where upload_id=" + list.get(i) + "");
            }
        }
        if (!mTableName.equals("PayMentOrder")) {
            deleteStable(mTableName, mTableId, jdbcTemplate);

            jsonArray.remove(0);
            StringBuffer sb = new StringBuffer();
            if (jsonArray.size() > 0) {
                for (int i = 0; i < jsonArray.size(); i++) {
                    JSONObject sTableData = jsonArray.getJSONObject(i);
                    String sTableName = sTableData.getString("cTableName");
                    JSONObject dataJSON = JSON.parseObject(sTableData.getString("obj"));
                    dataJSON.remove("_id");
                    dataJSON.remove("_uid");
                    dataJSON.remove("id");
                    sb.append("insert into " + sTableName + "(");
                    for (Entry<String, Object> maps : dataJSON.entrySet()) {
                        sb.append(" " + maps.getKey() + ",");
                    }
                    sb.append(mTableName + "_id");
                    sb.append(" ) values  ( ");
                    for (Entry<String, Object> entry : dataJSON.entrySet()) {
                        CreateTable sCreateTable = createTableDao.selectOne(new QueryWrapper<CreateTable>() {{
                            this.eq("tableName", sTableName);
                            this.eq("field", entry.getKey());
                        }});
                        //					System.out.println("createTable:" + sCreateTable);
                        //			System.out.println("key:" + entry.getKey());
                        if (sCreateTable.getType() != null) {
                            if ("datetime".equals(sCreateTable.getType())) {
                                if (entry.getValue() != null && !"".equals(entry.getValue())) {
                                    sb.append("'" + entry.getValue() + "',");
                                } else {
                                    sb.append("null,");
                                }
                            } else if ("varchar(8000)".equals(sCreateTable.getType()) && entry.getValue() != null) {
                                sb.append("'" + entry.getValue() + "',");
                            } else {
                                sb.append(entry.getValue() + ",");
                            }
                        }
                    }
                    sb.append(mTableId);
                    sb.append(");");
                }
                //		System.out.println("sb.toString():" + sb.toString());
                jdbcTemplate.update(sb.toString());
                if (list.size() > 0) {
                    for (int i = 0; i < list.size(); i++) {
                        jdbcTemplates.update("update Upload set joinName='" + mTableName + "' , joinid=" + mTableId + " where upload_id=" + list.get(i) + "");
                    }
                }
            }
        } else {
            jsonArray.remove(0);
            StringBuffer stringBuffer1 = new StringBuffer();
            if (jsonArray.size() > 0) {
                for (int i = 0; i < jsonArray.size(); i++) {
                    JSONObject sTableData = jsonArray.getJSONObject(i);
                    String sTableName = sTableData.getString("cTableName");
                    JSONObject dataJSON = JSON.parseObject(sTableData.getString("obj"));
                    Integer id = (Integer) dataJSON.get(sTableName + "_id");

                    if (sTableName.equals("ParticularsOfPayee")) {
                        dataJSON.remove(sTableName + "_id");
                        dataJSON.remove("PayMentOrder_id");
                    }
                    dataJSON.remove("_id");
                    dataJSON.remove("_uid");
                    dataJSON.remove("id");
                    stringBuffer1.append("update " + sTableName + " set ");

                    for (Entry<String, Object> entry : dataJSON.entrySet()) {
                        CreateTable createTables = createTableDao.selectOne(new QueryWrapper<CreateTable>() {{
                            this.eq("tableName", sTableName);
                            this.eq("field", entry.getKey());
                        }});
                        if ("varchar(8000)".equals(createTables.getType())) {
                            stringBuffer1.append(entry.getKey() + " = " + "'" + entry.getValue() + "',");
                        }
                        if ("numeric(18,5)".equals(createTables.getType())) {
                            stringBuffer1.append(entry.getKey() + " = " + entry.getValue() + ",");
                        }
                        if ("datetime".equals(createTables.getType())) {
                            if (entry.getValue() != null && !"".equals(entry.getValue())) {
                                stringBuffer1.append(entry.getKey() + " = " + "'" + entry.getValue() + "',");
                            } else {
                                stringBuffer1.append(entry.getKey() + " = " + "null,");
                            }
                        }
                    }
                    stringBuffer1.delete(stringBuffer1.toString().lastIndexOf(","), stringBuffer1.length());
                    stringBuffer1.append(" where " + sTableName + "_id=" + id);
                    jdbcTemplate.update(stringBuffer1.toString());
                    if (list.size() > 0) {
                        for (int c = 0; c < list.size(); c++) {
                            jdbcTemplates.update("update Upload set joinName='" + mTableName + "' , joinid=" + mTableId + " where upload_id=" + list.get(c) + "");
                        }
                    }
                }
            }
        }
        return reJson;
    }


    @SuppressWarnings("all")
    public JSONArray findAll(int formListId) throws Exception {
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletRequest request = attributes.getRequest();
        HttpSession session = request.getSession();
        UserInfo userInfo = (UserInfo) session.getAttribute(SystemEnum.SESSION_USER.getValue());
        /////////////////////////////
        //过滤可用金额为0的数据
        JdbcTemplate jdbcTemplate = msFactory.getJdbcTemplate();
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList("select * from form_list where formListId=" + formListId);
        FormList formList1 = formListDao.selectById(formListId);
        String tableName = formList1.getCTableName();
        List<TableClass> tableClassDaoByTableName = tableClassDao.selectList(new QueryWrapper<TableClass>() {{
            this.eq("tableClassName", tableName);
        }});
        TableClass tableClass1 = tableClassDaoByTableName.get(0);

        JdbcTemplate jdbcTemplate1 = dbServer.getJdbcTemplate(tableClass1.getDbInfoId());
        StringBuffer stringBuffer3 = new StringBuffer();
        if ("ApplicationFund".equals(tableName)) {
//			stringBuffer3.append("select *,ApplicationFund_xgry_0003 - ApplicationFund_xgry_0011 - ApplicationFund_xgry_0014 as ApplicationFund_xgry_00 from " + tableName + "");
            stringBuffer3.append("select * from " + tableName + " where ApplicationFund_state = 3");
            JSONArray fromDataList = new JSONArray();
            if (userInfo.getUserName().equals("admin")) {
                //		System.out.println("vvvvvvvvvv" + stringBuffer3);
                List<Map<String, Object>> mapList1 = jdbcTemplate1.queryForList(stringBuffer3.toString());
                if (mapList1.size() > 0) {
                    for (int i = 0; i < mapList1.size(); i++) {
                        BigDecimal applicationFund_xgry_0015 = (BigDecimal) mapList.get(i).get("ApplicationFund_xgry_0015");
                        if (applicationFund_xgry_0015.equals(BigDecimal.ZERO)) {
                            mapList1.remove(i);
                        }
                    }
                }
                return JSONArray.parseArray(JSON.toJSONString(mapList1));
            } else {
                stringBuffer3.append(" and ApplicationFund_single_person='" + userInfo.getUserName() + "'");
                List<UserinfoJurisdiction> mmlist = userinfo_jurisdictionDao.selectList(new QueryWrapper<UserinfoJurisdiction>() {{
                    this.eq("userNumber", userInfo.getUserName());
                }});


                if (mmlist.size() > 0) {
                    for (UserinfoJurisdiction userinfoJurisdiction : mmlist) {
                        stringBuffer3.append(" or ApplicationFund_single_person='" + userinfoJurisdiction.getUserjurisdiction() + "'");
                    }
                }

                List<Map<String, Object>> mapList1 = jdbcTemplate1.queryForList(stringBuffer3.toString());
                if (mapList1.size() > 0) {
                    for (int i = 0; i < mapList1.size(); i++) {
                        BigDecimal applicationFund_xgry_0015 = (BigDecimal) mapList1.get(i).get("ApplicationFund_xgry_0015");
                        if (applicationFund_xgry_0015.compareTo(BigDecimal.ZERO) == 0) {
                            mapList1.remove(i);
                        }
                    }
                }
                return JSONArray.parseArray(JSON.toJSONString(mapList1));
            }

        }
        ////////////////

        JSONArray jsonArray = new JSONArray();

        StringBuffer stringBuffer = new StringBuffer();

        JSONArray formList = JSONArray.parseArray(JSON.toJSONString(mapList));
        JSONObject jsonObject = formList.getJSONObject(0);
        String mTable = jsonObject.getString("cTableName");
//		System.out.println("mTable:" + mTable);
        List<Map<String, Object>> tableList = jdbcTemplate.queryForList("select * from table_class where tableClassName='" + mTable + "'");
        JSONArray cols = JSON.parseArray(JSON.toJSONString(tableList));
        JSONObject jdbcJSON = cols.getJSONObject(0);

        JdbcTemplate dbServerJdbcTemplate = dbServer.getJdbcTemplate(jdbcJSON.getInteger("dbInfoId"));
        int mTableId = jdbcJSON.getInteger("tableClassId");

        List<CreateTable> createTables = createTableDao.selectList(new QueryWrapper<CreateTable>() {{
            this.eq("tableName", mTable);
        }});
        TableClass tableClass = tableClassDao.selectOne(new QueryWrapper<TableClass>() {{
            this.eq("tableClassName", mTable);
        }});
        List<TableClass> sTableList = tableClassDao.selectList(new QueryWrapper<TableClass>() {{
            this.eq("mTableId", tableClass.getTableClassId());
        }});
        if (sTableList.size() > 0) {
            for (int c = 0; c < sTableList.size(); c++) {
                String tableClassName = sTableList.get(c).getTableClassName();
                List<CreateTable> createTables1 = createTableDao.selectList(new QueryWrapper<CreateTable>() {{
                    this.eq("tableName", tableClassName);
                }});
                for (int d = 0; d < createTables1.size(); d++) {
                    CreateTable ct = createTables1.get(d);
                    createTables.add(ct);
                }
            }
        }

        stringBuffer.append("select ");
        stringBuffer.append(mTable + "." + mTable + "_id as " + mTable + "_id,");
        for (int b = 0; b < createTables.size(); b++) {
            CreateTable createTable = createTables.get(b);
            FormLtem formltems = formItemsDao.selectOne(new QueryWrapper<FormLtem>() {{
                this.eq("cTableField", createTable.getField());
                this.eq("cTableName", mTable);
            }});
            if ("datetime".equals(createTable.getType())) {
                stringBuffer.append("( select convert(varchar(100)," + createTable.getField() + ",120)) as " + createTable.getField() + ",");
            } else if (formltems != null) {
                if (!"{}".equals(formltems.getRef())) {
                    JSONObject refJSON = JSON.parseObject(formltems.getRef());
                    JSONObject ref = JSON.parseObject(refJSON.getString("ref"));
                    stringBuffer.append("(select " + ref.getString("cTableField") + " from " + ref.getString("cTableName") + " where "
                            + ref.getString("cTableName") + "_id=" + mTable + "." + formltems.getCTableField() + " ) as " + createTable.getField() + ",");
                } else {
                    stringBuffer.append(mTable + "." + createTable.getField() + " as " + createTable.getField() + ",");
                }
            } else if (createTable.getField() == "parent_level") {
                stringBuffer.append("(select  " + createTable.getTableName() + "_name from " + createTable.getTableName() + " where " + createTable.getTableName() + "_id =" + createTable.getTableName() + ")");
            }
        }
        stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
        stringBuffer.append(" from " + mTable);
        if (sTableList.size() > 0) {
            JSONArray tableJsonArr = JSONArray.parseArray(JSON.toJSONString(sTableList));
            for (int a = 0; a < tableJsonArr.size(); a++) {
                JSONObject tableJsonObject = tableJsonArr.getJSONObject(a);
                String sTableName = sTableList.get(a).getTableClassName();
                stringBuffer.append(" left join " + sTableName);
                stringBuffer.append(" on " + mTable + "." + mTable + "_id=" + sTableName + "." + sTableName + "_id");

            }
        }
        JSONArray fromDataList = new JSONArray();
        if (jsonObject.getString("formTypes").equals("form")) {
            if (userInfo.getUserName().equals("admin")) {


                List<Map<String, Object>> formDataList = dbServerJdbcTemplate.queryForList(stringBuffer.toString());

                fromDataList = JSONArray.parseArray(JSON.toJSONString(formDataList));
            } else {
                stringBuffer.append(" where " + mTable + "_single_person='" + userInfo.getUserName() + "'");
                List<UserinfoJurisdiction> mmlist = userinfo_jurisdictionDao.selectList(new QueryWrapper<UserinfoJurisdiction>() {{
                    this.eq("userNumber", userInfo.getUserName());
                }});

                if (mapList.size() > 0) {
                    for (UserinfoJurisdiction userinfoJurisdiction : mmlist) {
                        stringBuffer.append(" or " + mTable + "_single_person='" + userinfoJurisdiction.getUserjurisdiction() + "'");
                    }
                }
                //			System.out.println("ccccccccccccccccccccccc" + stringBuffer.toString());
                List<Map<String, Object>> formDataList = dbServerJdbcTemplate.queryForList(stringBuffer.toString());

                fromDataList = JSONArray.parseArray(JSON.toJSONString(formDataList));
            }

        } else {
            List<DataPermissions> dataPermissionsList = dataPermissionsDao.selectList(new QueryWrapper<DataPermissions>() {{
                this.eq("data_userNumber", userInfo.getUserName());
            }});
            List<Map<String, Object>> mapListu = jdbcTemplate.queryForList("select cTreeStruct from form_list where formListId=" + formListId + "");
            JSONArray jsonArrayu = JSON.parseArray(JSON.toJSONString(mapListu));
            JSONObject jsonObjectu = jsonArrayu.getJSONObject(0);
            String table = jsonObjectu.getString("cTreeStruct");
            String tables[] = table.replace("[", "").replace("]", "").split(",");
            List list = new ArrayList();
            for (int s = 0; s < tables.length; s++) {
                list.add(tables[s]);
            }
            if (dataPermissionsList.size() > 0) {
                stringBuffer.append(" where ");
                List<Map<String, Object>> mapList5 = null;
                DataPermissions dataPermissions = null;
                for (int i = 0; i < dataPermissionsList.size(); i++) {
                    dataPermissions = dataPermissionsList.get(i);
                    mapList5 = dbServerJdbcTemplate.queryForList("SELECT * FROM syscolumns WHERE id=OBJECT_ID('" + dataPermissions.getTableName() + "') AND name='" + mTable + "_id'");
                    List<Map<String, Object>> mapList1 = dbServerJdbcTemplate.queryForList("select  * from  " + dataPermissions.getTableName() + " where " + dataPermissions.getTableName() + "_id =" + dataPermissions.getId() + "");
                    JSONArray jsonArray1 = JSON.parseArray(JSON.toJSONString(mapList1));
                    JSONObject jsonObject2 = jsonArray1.getJSONObject(0);
                    List<Map<String, Object>> mapList2 = dbServerJdbcTemplate.queryForList("select *  from  " + dataPermissions.getTableName() + "");
                    JSONArray jsonArray2 = JSON.parseArray(JSON.toJSONString(mapList2));
                    String a = dataPermissions.getId();
                    String b = dataPermissions.getTableName() + "_id";
                    String l = dataPermissions.getTableName() + "_id";
                    String g = dataPermissions.getId();


                    mapList5 = dbServerJdbcTemplate.queryForList("SELECT * FROM syscolumns WHERE id=OBJECT_ID('" + mTable + "') AND name='" + dataPermissions.getTableName() + "_id'");
                    if (mapList5.size() > 0) {
                        stringBuffer.append("" + dataPermissions.getTableName() + "_id = " + jsonObject2.getString("" + dataPermissions.getTableName() + "_id"));
                        stringBuffer.append(" or ");
                        for (int c = 0; c < jsonArray2.size(); c++) {
                            JSONObject jsonObject3 = jsonArray2.getJSONObject(c);
                            List<Map<String, Object>> mapList0 = dbServerJdbcTemplate.queryForList("select  * from  " + dataPermissions.getTableName() + " where " + dataPermissions.getTableName() + "_id" + " =" + dataPermissions.getId() + "");
                            JSONArray jsonArray0 = JSON.parseArray(JSON.toJSONString(mapList0));
                            JSONObject jsonObject0 = jsonArray0.getJSONObject(0);
                            if (jsonObject0.getString("parent_level").equals(jsonObject3.getString("" + dataPermissions.getTableName() + "_id"))) {

                                stringBuffer.append(" " + dataPermissions.getTableName() + "_id =" + jsonObject3.getString("" + dataPermissions.getTableName() + "_id") + "");
                                stringBuffer.append(" or ");
//								a = jsonObject0.getString("parent_level");
                            } else if (jsonObject0.getString("" + dataPermissions.getTableName() + "_id").equals(jsonObject3.getString("parent_level"))) {
                                stringBuffer.append("" + dataPermissions.getTableName() + "_id = " + jsonObject3.getString("" + dataPermissions.getTableName() + "_id") + "");
                                stringBuffer.append(" or ");
//								l = "parent_level";
//								a = jsonObject0.getString("" + dataPermissions.getTableName() + "_id");
                            }
                        }
                        for (int c = 0; c < jsonArray2.size(); c++) {
                            JSONObject jsonObject3 = jsonArray2.getJSONObject(c);
                            List<Map<String, Object>> mapList0 = dbServerJdbcTemplate.queryForList("select  * from  " + dataPermissions.getTableName() + " where " + l + "" + " =" + a + "");
                            JSONArray jsonArray0 = JSON.parseArray(JSON.toJSONString(mapList0));
                            JSONObject jsonObject0 = jsonArray0.getJSONObject(0);
                            if (jsonObject0.getString("parent_level").equals(jsonObject3.getString("" + dataPermissions.getTableName() + "_id"))) {

                                stringBuffer.append(" " + dataPermissions.getTableName() + "_id =" + jsonObject3.getString("" + dataPermissions.getTableName() + "_id") + "");
                                stringBuffer.append(" or ");
                                a = jsonObject0.getString("parent_level");
                            } else if (jsonObject0.getString("" + dataPermissions.getTableName() + "_id").equals(jsonObject3.getString("parent_level"))) {
                                stringBuffer.append("" + dataPermissions.getTableName() + "_id = " + jsonObject3.getString("" + dataPermissions.getTableName() + "_id") + "");
                                stringBuffer.append(" or ");
                                l = "parent_level";
                                a = jsonObject0.getString("" + dataPermissions.getTableName() + "_id");
                            }
                        }

                    } else if (list.contains(dataPermissions.getTableName())) {
                        List<Map<String, Object>> mapList0 = dbServerJdbcTemplate.queryForList("select  * from  " + dataPermissions.getTableName() + " where " + dataPermissions.getTableName() + "_id" + " =" + dataPermissions.getId() + "");
                        JSONArray jsonArray3 = JSON.parseArray(JSON.toJSONString(mapList0));
                        JSONObject jsonObject3 = jsonArray3.getJSONObject(0);
                        stringBuffer.append("  " + mTable + "_id=" + jsonObject3.getString("" + dataPermissions.getTableName() + "_id") + "");
                        stringBuffer.append(" or ");
                    }

                }
                if (mapList5.size() > 0 || list.contains(dataPermissions.getTableName())) {
                    stringBuffer.delete(stringBuffer.toString().lastIndexOf("or"), stringBuffer.length());
                } else {
                    stringBuffer.delete(stringBuffer.toString().lastIndexOf("where"), stringBuffer.length());
                }

            }
        }
        List<Map<String, Object>> formDataList = dbServerJdbcTemplate.queryForList(stringBuffer.toString());
        fromDataList = JSONArray.parseArray(JSON.toJSONString(formDataList));
        return fromDataList;
    }


    @SuppressWarnings("all")
    public JSONArray findById(JSONObject jsonObject) throws Exception {
        JSONArray jsonArray = new JSONArray();
        JdbcTemplate jdbcTemplate = msFactory.getJdbcTemplate();
        StringBuffer stringBuffer = new StringBuffer();
        StringBuffer stringBuffer1 = new StringBuffer();
        String mTable = jsonObject.getString("cTableName");
        List<Map<String, Object>> tableList = jdbcTemplate.queryForList("select * from table_class where tableClassName = '"
                + mTable + "'");
        JSONArray cols = JSON.parseArray(JSON.toJSONString(tableList));
        JSONObject jdbcJSON = cols.getJSONObject(0);
        JdbcTemplate dbServerJdbcTemplate = dbServer.getJdbcTemplate(jdbcJSON.getInteger("dbInfoId"));
        int mTableId = jdbcJSON.getInteger("tableClassId");
        List<Map<String, Object>> sTableList = jdbcTemplate.queryForList("select * from table_class where mTableId=" + mTableId);
        // TODO   select *需更改表单字段+主键id
        stringBuffer.append("select ");
        stringBuffer.append(mTable + "_id as " + mTable + "_id,");
        List<CreateTable> createTables = createTableDao.selectList(new QueryWrapper<CreateTable>() {{
            this.eq("tableName", mTable);
        }});
        for (int i = 0; i < createTables.size(); i++) {
            CreateTable createTable = createTables.get(i);
            if ("datetime".equals(createTable.getType())) {
                stringBuffer.append("( select convert(varchar(100)," + createTable.getField() + ",120)) as " + createTable.getField() + ",");
            } else {
                stringBuffer.append(createTable.getField() + " as " + createTable.getField() + ",");
            }

        }

        stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
        stringBuffer.append(" from " + mTable + " where " + mTable + "_id=" + jsonObject.getInteger("id"));
        List<Map<String, Object>> formDataList = dbServerJdbcTemplate.queryForList(stringBuffer.toString());
        //		System.out.println("大豆:"+stringBuffer);
//			System.out.println("小豆:"+formDataList);
        JSONArray fromDataList = JSONArray.parseArray(JSON.toJSONString(formDataList));
        JSONObject retuJson = new JSONObject();
        retuJson.put(mTable, fromDataList);

        //查询name
        stringBuffer1.append("select ");
        stringBuffer1.append(mTable + "_id as " + mTable + "_id,");

        for (int d = 0; d < createTables.size(); d++) {

            CreateTable createTable = createTables.get(d);
            FormLtem formltems = formItemsDao.selectOne(new QueryWrapper<FormLtem>() {{
                this.eq("cTableField", createTable.getField());
                this.eq("cTableName", mTable);
            }});
            if ("datetime".equals(createTable.getType())) {
                stringBuffer1.append("( select convert(varchar(100)," + createTable.getField() + ",120)) as " + createTable.getField() + ",");
            } else if (formltems != null) {
                if (!"{}".equals(formltems.getRef())) {
                    JSONObject refJSON = JSON.parseObject(formltems.getRef());
                    JSONObject ref = JSON.parseObject(refJSON.getString("ref"));
                    stringBuffer1.append("(select " + ref.getString("cTableField") + " from " + ref.getString("cTableName") + " where "
                            + ref.getString("cTableName") + "_id=" + mTable + "." + formltems.getCTableField() + " ) as " + createTable.getField() + ",");
                } else {
                    stringBuffer1.append(mTable + "." + createTable.getField() + " as " + createTable.getField() + ",");
                }
            } else if (createTable.getField() == "parent_level") {
                stringBuffer1.append("(select  " + createTable.getTableName() + "_name from " + createTable.getTableName() + " where " + createTable.getTableName() + "_id =" + createTable.getTableName() + ")");

            }

        }

        stringBuffer1.delete(stringBuffer1.toString().lastIndexOf(","), stringBuffer1.length());
        stringBuffer1.append(" from " + mTable + " where " + mTable + "_id=" + jsonObject.getInteger("id"));
        List<Map<String, Object>> formDataList1 = dbServerJdbcTemplate.queryForList(stringBuffer1.toString());
        //	System.out.println("ddddddd" + formDataList1);

        //id
        HashMap<String, Object> map1 = new HashMap<>();
        for (Map<String, Object> map : formDataList) {
            for (Entry<String, Object> ml : map.entrySet()) {
                String key = ml.getKey();

                Object value = ml.getValue();
                map1.put(key, value);

            }
        }

        String g = mTable + "_single_person";
        List<Map<String, Object>> olist = jdbcTemplate.queryForList("select * from user_info where userName='" + map1.get(g) + "'");
        JSONArray oli = JSON.parseArray(JSON.toJSONString(olist));
        JSONObject olil = oli.getJSONObject(0);
        Object t = olil.getString("uName");

//		System.out.println("oooooooooooooo" + olist);

//		System.out.println("map1++" + map1);
        //name
        HashMap<String, Object> map2 = new HashMap<>();

        for (Map<String, Object> map : formDataList1) {
            for (Entry<String, Object> ml1 : map.entrySet()) {
                String key = ml1.getKey();
                Object value = ml1.getValue();

                map2.put(key, value);
                map2.put(g, t);
            }

        }
        //System.out.println("map2++" + map2);
        HashMap<String, Object> map3 = new HashMap<>();
        for (Object o : map1.keySet()) {
            if (map2.containsKey(o)) {
                map3.put((String) o, map1.get(o) + "_" + map2.get(o));
            }
        }
        retuJson.put(mTable, map3);

        if (sTableList.size() > 0) {
            JSONArray tableJsonArr = JSONArray.parseArray(JSON.toJSONString(sTableList));
            for (int a = 0; a < tableJsonArr.size(); a++) {
                JSONObject sTableJsonObject = tableJsonArr.getJSONObject(a);
                StringBuffer sTablesql = new StringBuffer();

                String sTableName = sTableJsonObject.getString("tableClassName");
                List<CreateTable> sCreateTable = createTableDao.selectList(new QueryWrapper<CreateTable>() {{
                    this.eq("tableName", sTableName);
                }});
                sTablesql.append("select ");
                for (int v = 0; v < sCreateTable.size(); v++) {
                    CreateTable createTable = sCreateTable.get(v);
                    if ("datetime".equals(createTable.getType())) {
                        sTablesql.append("( select convert(varchar(100)," + createTable.getField() + ",120)) as " + createTable.getField() + ",");
                    } else {
                        sTablesql.append(createTable.getField() + " as " + createTable.getField() + ",");
                    }
                }

                sTablesql.delete(sTablesql.toString().lastIndexOf(","), sTablesql.length());
                sTablesql.append(" from " + sTableName + " where " + mTable + "_id=" + jsonObject.getInteger("id"));
                List<Map<String, Object>> sTableData = dbServerJdbcTemplate.queryForList(sTablesql.toString());
                sTablesql.append(" from " + sTableName + " where " + mTable + "_id=" + jsonObject.getInteger("id"));
                JSONArray sTableDataList = JSONArray.parseArray(JSON.toJSONString(sTableData));
                retuJson.put(sTableName, sTableDataList);
                //	System.out.println("sql");

            }
        }

        jsonArray.add(retuJson);
        //	System.out.println("返回值" + jsonArray.toJSONString());
        return jsonArray;
    }

    @SuppressWarnings("all")
    public JSONArray referenceTest(JSONObject jsonObject) throws Exception {
        JdbcTemplate msFactoryJdbcTemplate = msFactory.getJdbcTemplate();
        String cTableName = jsonObject.getString("cTableName");
        String field = jsonObject.getString("field");
        String type = jsonObject.getString("type");
        List<Map<String, Object>> maps = msFactoryJdbcTemplate.queryForList("select * from table_class where tableClassName='"
                + cTableName + "'");
        JSONArray mapsJsonArray = JSON.parseArray(JSON.toJSONString(maps));
        JSONObject mapsJsonObject = mapsJsonArray.getJSONObject(0);
        JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(mapsJsonObject.getInteger("dbInfoId"));
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select * from ");
        stringBuffer.append(cTableName);
        stringBuffer.append(" where " + field + " =");
        if (type != "int") {
            stringBuffer.append("'" + jsonObject.getString("value") + "'");
        } else {
            stringBuffer.append(jsonObject.getInteger("value"));
        }
        List<Map<String, Object>> reJson = jdbcTemplate.queryForList(stringBuffer.toString());
        JSONArray reJsonArray = JSON.parseArray(JSON.toJSONString(reJson));
        return reJsonArray;
    }


    @Override
    public JSONArray casaedNew(JSONObject jsonObject) throws Exception {
        //需要查询的字段
        String fileld = jsonObject.getString("fileld");
        String[] cTableNameLength = fileld.split("_");
        String returnFileld = jsonObject.getString("returnFileld");
        String cTableName = cTableNameLength[0];
        String fileldValue = jsonObject.getString("fileldValue");
        String fileldV = jsonObject.getString("fileldV");
        List<TableClass> tableClass = tableClassDao.selectList(new QueryWrapper<TableClass>() {{
            this.eq("tableClassName", cTableName);
        }});
        JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(tableClass.get(0).getDbInfoId());
//			String str = "select " + fileld + " as " + returnFileld + " from " + cTableName + " where " + fileldV + " = '" + fileldValue + "'";
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select " + fileld);
        stringBuffer.append(" as " + returnFileld);
        stringBuffer.append(" from " + cTableName + " where " + fileldV);
//		stringBuffer.append(" from " + cTableName + " where " + cTableName+"_id");
        String[] fileldVs = fileldV.split("_");
        if (fileldVs[1] == "id") {
            stringBuffer.append("=" + fileldValue);
        } else {
            stringBuffer.append("='" + fileldValue + "'");
        }

        System.out.println("sql:" + stringBuffer.toString());
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(stringBuffer.toString());
        Map<String, Object> map = mapList.get(0);
        map.put("returnFileld", returnFileld);
        mapList.add(map);
        JSONArray returnJson = JSON.parseArray(JSON.toJSONString(mapList));
        return returnJson;
    }

    //	@Override
    public JSONObject Select(JSONArray jsonArray) throws Exception {

        JSONObject jsonObject0 = new JSONObject();
        //	System.out.println(jsonArray);
        JSONArray jsonArray1 = jsonArray.getJSONArray(0);
        JSONObject jsonObject = jsonArray1.getJSONObject(0);

        String tableName = jsonObject.getString("cTableName");
        JdbcTemplate msJdbc = msFactory.getJdbcTemplate();
        List<Map<String, Object>> maps = msJdbc.queryForList("select * from table_class where tableClassName = '" + tableName + "'");
        JSONArray cols = JSON.parseArray(JSON.toJSONString(maps));
        JSONObject jsjson = cols.getJSONObject(0);
        JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(jsjson.getInteger("dbInfoId"));
        JSONArray jsonArray2 = jsonArray.getJSONArray(0);
        JSONObject jo = jsonArray2.getJSONObject(0);
        JSONObject jo1 = jo.getJSONObject("obj");
        String ApplicationDepartment = jo1.getString("ExpensesReimbursement_ApplicationDepartment");
//		System.out.println("jo:" + jo);
        for (int i = 1; i < jsonArray2.size(); i++) {
            JSONObject json = jsonArray2.getJSONObject(i);
            JSONObject jy = json.getJSONObject("obj");
            String tablename1 = json.getString("cTableName");
            String money0 = jy.getString("ReimbursementInformation_xgry_0003");
            String xm = jy.getString("ReimbursementInformation_xgry_0001");
            List<Map<String, Object>> o = jdbcTemplate.queryForList("select sum (" + tablename1 + ".ReimbursementInformation_xgry_0003) xgry_003," + tableName + ".ExpensesReimbursement_ApplicationDepartment ea," + tablename1 + ".ReimbursementInformation_xgry_0001 xgry_0001 from  " + tablename1 + " left join " + tableName + " on " + tablename1 + ".ExpensesReimbursement_id=" + tableName + ".ExpensesReimbursement_id   where  " + tableName + ".ExpensesReimbursement_ApplicationDepartment='" + ApplicationDepartment + "' and   " + tablename1 + ".ReimbursementInformation_xgry_0001='" + xm + "' GROUP BY " + tableName + ".ExpensesReimbursement_ApplicationDepartment," + tablename1 + ".ReimbursementInformation_xgry_0001");
            JSONArray jsonarray = JSON.parseArray(JSON.toJSONString(o));

            JSONObject jsonObject2 = jsonarray.getJSONObject(0);
            String money = jsonObject2.getString("xgry_003");
            String bumen = jsonObject2.getString("ea");
            String xiangmu = jsonObject2.getString("xgry_0001");
//			System.out.println("xxxx" + xiangmu);
            List<Map<String, Object>> mapList = jdbcTemplate.queryForList("select * from  BudgetApplicationChile left join  BudgetApplication on BudgetApplicationChile.BudgetApplication_id==BudgetApplication.BudgetApplication_id where BudgetApplication.BudgetApplication_ApplicationDepartment='" + bumen + "' and BudgetApplicationChile.BudgetApplicationChile_BudgetItems='" + xiangmu + "' group by BudgetApplication.BudgetApplication_ApplicationDepartment ");
            //		System.out.println(mapList);
            JSONArray jsonArray3 = JSON.parseArray(JSON.toJSONString(mapList));
            //		System.out.println("jsonArray:   " + jsonArray3);
            JSONObject jsonObject3 = jsonArray3.getJSONObject(0);
            String money1 = jsonObject3.getString("BudgetApplicationChile_BudgetAmount");
            String bumen1 = jsonObject3.getString("BudgetApplication_ApplicationDepartment");
            String xiangmu1 = jsonObject3.getString("BudgetApplicationChile_BudgetItems");
            int summoney = Integer.parseInt(money0) + Integer.parseInt(money);
            if (summoney > Integer.parseInt(money1)) {
                jsonObject0.put("datate", "部门为" + bumen1 + "项目是" + xiangmu1 + "申请额度超出预期");
            }
        }
        return jsonObject0;
    }


    /**
     * 主表具体数据内容查询
     *
     * @param formListId
     * @return
     * @throws Exception
     */
    @SuppressWarnings("all")
    public JSONArray findsAll(int formListId) throws Exception {
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletRequest request = attributes.getRequest();
        HttpSession session = request.getSession();
        UserInfo userInfo = (UserInfo) session.getAttribute(SystemEnum.SESSION_USER.getValue());
        JSONArray jsonArray = new JSONArray();
        JSONObject retuJson = new JSONObject();
        JdbcTemplate jdbcTemplate = msFactory.getJdbcTemplate();
        StringBuffer stringBuffer = new StringBuffer();
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList("select * from form_list where formListId=" + formListId);
        JSONArray formList = JSONArray.parseArray(JSON.toJSONString(mapList));
        JSONObject jsonObject = formList.getJSONObject(0);
        String mTable = jsonObject.getString("cTableName");
        //	System.out.println("mTable:" + mTable);
        List<Map<String, Object>> tableList = jdbcTemplate.queryForList("select * from table_class where tableClassName='" + mTable + "'");
        JSONArray cols = JSON.parseArray(JSON.toJSONString(tableList));
        JSONObject jdbcJSON = cols.getJSONObject(0);
        Integer dbInfoId = jdbcJSON.getInteger("dbInfoId");
        JdbcTemplate dbServerJdbcTemplate = dbServer.getJdbcTemplate(jdbcJSON.getInteger("dbInfoId"));
        int mTableId = jdbcJSON.getInteger("tableClassId");
        List<Map<String, Object>> sTableList = jdbcTemplate.queryForList("select * from table_class where mTableId=" + mTableId);
        List<CreateTable> createTables = createTableDao.selectList(new QueryWrapper<CreateTable>() {{
            this.eq("tableName", mTable);
        }});
        TableClass tableClass = tableClassDao.selectOne(new QueryWrapper<TableClass>() {{
            this.eq("tableClassName", mTable);
        }});
        stringBuffer.append("select ");
        stringBuffer.append(mTable + "." + mTable + "_id as " + mTable + "_id,");
        for (int b = 0; b < createTables.size(); b++) {
            CreateTable createTable = createTables.get(b);
            FormLtem formltems = formItemsDao.selectOne(new QueryWrapper<FormLtem>() {{
                this.eq("cTableField", createTable.getField());
                this.eq("cTableName", mTable);
            }});
            if ("datetime".equals(createTable.getType())) {
                stringBuffer.append("( select convert(varchar(100)," + createTable.getField() + ",120)) as " + createTable.getField() + ",");
            } else if (formltems != null) {
                if (!"{}".equals(formltems.getRef())) {
                    JSONObject refJSON = JSON.parseObject(formltems.getRef());
                    JSONObject ref = JSON.parseObject(refJSON.getString("ref"));
                    stringBuffer.append("(select " + ref.getString("cTableField") + " from " + ref.getString("cTableName") + " where "
                            + ref.getString("cTableName") + "_id=" + mTable + "." + formltems.getCTableField() + " ) as " + createTable.getField() + ",");
                } else {
                    stringBuffer.append(mTable + "." + createTable.getField() + " as " + createTable.getField() + ",");
                }
            } else if (createTable.getField() == "parent_level") {
                stringBuffer.append("(select  " + mTable + "_name from " + mTable + " where " + mTable + "_id =" + mTable + ")");
            }

        }
        stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
        stringBuffer.append(" from " + mTable);
        JSONArray fromDataList = new JSONArray();
//			if (jsonObject.getString("formTypes").equals("form")) {
//				if (userInfo.getUserName().equals("admin")) {
//					System.out.println("sddddddddddddddddddddd" + stringBuffer);
//					List<Map<String, Object>> formDataList = dbServerJdbcTemplate.queryForList(stringBuffer.toString());
//
//				}
//				stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
//				stringBuffer.append(" from " + mTable);
        if (jsonObject.getString("formTypes").equals("form")) {
            if (userInfo.getUserName().equals("admin")) {
                //			System.out.println("sssss"+stringBuffer);
                List<Map<String, Object>> formDataList = dbServerJdbcTemplate.queryForList(stringBuffer.toString());
                fromDataList = JSONArray.parseArray(JSON.toJSONString(formDataList));
            } else {
                stringBuffer.append(" where ");
                List<UserinfoJurisdiction> mmlist = userinfo_jurisdictionDao.selectList(new QueryWrapper<UserinfoJurisdiction>() {{
                    this.eq("userNumber", userInfo.getUserName());
                }});

                for (UserinfoJurisdiction userinfoJurisdiction : mmlist) {
                    stringBuffer.append(" " + mTable + "_single_person='" + userinfoJurisdiction.getUserjurisdiction() + "'");
                    stringBuffer.append(" or ");
                }

                stringBuffer.append(" " + mTable + "_single_person='" + userInfo.getUserName() + "'");
            }

        } else {
            List<DataPermissions> json = dataPermissionsDao.selectList(new QueryWrapper<DataPermissions>() {{
                this.eq("data_userNumber", userInfo.getUserName());
            }});
            List<Map<String, Object>> mapListu = jdbcTemplate.queryForList("select cTreeStruct from form_list where formListId=" + formListId + "");
            JSONArray jsonArrayu = JSON.parseArray(JSON.toJSONString(mapListu));
            JSONObject jsonObjectu = jsonArrayu.getJSONObject(0);
            String table = jsonObjectu.getString("cTreeStruct");
            String tables[] = table.replace("[", "").replace("]", "").split(",");
            List list = new ArrayList();
            for (int s = 0; s < tables.length; s++) {
                list.add(tables[s]);
            }
            if (json.size() > 0) {
                stringBuffer.append(" where ");
                List<Map<String, Object>> mapList5 = null;
                DataPermissions jsonObject1 = null;
                for (int i = 0; i < json.size(); i++) {
                    jsonObject1 = json.get(i);
                    mapList5 = dbServerJdbcTemplate.queryForList("SELECT * FROM syscolumns WHERE id=OBJECT_ID('" + jsonObject1.getTableName() + "') AND name='" + mTable + "_id'");
                    List<Map<String, Object>> mapList1 = dbServerJdbcTemplate.queryForList("select  * from  " + jsonObject1.getTableName() + " where " + jsonObject1.getTableName() + "_id =" + jsonObject1.getId() + "");
                    JSONArray jsonArray1 = JSON.parseArray(JSON.toJSONString(mapList1));
                    JSONObject jsonObject2 = jsonArray1.getJSONObject(0);
                    List<Map<String, Object>> mapList2 = dbServerJdbcTemplate.queryForList("select *  from  " + jsonObject1.getTableName() + "");
                    JSONArray jsonArray2 = JSON.parseArray(JSON.toJSONString(mapList2));
                    String a = jsonObject1.getId();
                    String b = jsonObject1.getTableName() + "_id";
                    String l = jsonObject1.getTableName() + "_id";
                    String g = jsonObject1.getId();


                    mapList5 = dbServerJdbcTemplate.queryForList("SELECT * FROM syscolumns WHERE id=OBJECT_ID('" + mTable + "') AND name='" + jsonObject1.getTableName() + "_id'");
                    if (mapList5.size() > 0) {
                        stringBuffer.append("" + jsonObject1.getTableName() + "_id = " + jsonObject2.getString("" + jsonObject1.getTableName() + "_id"));
                        stringBuffer.append(" or ");
                        for (int c = 0; c < jsonArray2.size(); c++) {
                            JSONObject jsonObject3 = jsonArray2.getJSONObject(c);
                            List<Map<String, Object>> mapList0 = dbServerJdbcTemplate.queryForList("select  * from  " + jsonObject1.getTableName() + " where " + jsonObject1.getTableName() + "_id" + " =" + jsonObject1.getId() + "");
                            JSONArray jsonArray0 = JSON.parseArray(JSON.toJSONString(mapList0));
                            JSONObject jsonObject0 = jsonArray0.getJSONObject(0);
                            if (jsonObject0.getString("parent_level").equals(jsonObject3.getString("" + jsonObject1.getTableName() + "_id"))) {

                                stringBuffer.append(" " + jsonObject1.getTableName() + "_id =" + jsonObject3.getString("" + jsonObject1.getTableName() + "_id") + "");
                                stringBuffer.append(" or ");
//								a = jsonObject0.getString("parent_level");
                            } else if (jsonObject0.getString("" + jsonObject1.getTableName() + "_id").equals(jsonObject3.getString("parent_level"))) {
                                stringBuffer.append("" + jsonObject1.getTableName() + "_id = " + jsonObject3.getString("" + jsonObject1.getTableName() + "_id") + "");
                                stringBuffer.append(" or ");
//								l = "parent_level";
//								a = jsonObject0.getString("" + jsonObject1.getTableName() + "_id");
                            }
                        }
                        for (int c = 0; c < jsonArray2.size(); c++) {
                            JSONObject jsonObject3 = jsonArray2.getJSONObject(c);
                            List<Map<String, Object>> mapList0 = dbServerJdbcTemplate.queryForList("select  * from  " + jsonObject1.getTableName() + " where " + l + "" + " =" + a + "");
                            JSONArray jsonArray0 = JSON.parseArray(JSON.toJSONString(mapList0));
                            JSONObject jsonObject0 = jsonArray0.getJSONObject(0);
                            if (jsonObject0.getString("parent_level").equals(jsonObject3.getString("" + jsonObject1.getTableName() + "_id"))) {

                                stringBuffer.append(" " + jsonObject1.getTableName() + "_id =" + jsonObject3.getString("" + jsonObject1.getTableName() + "_id") + "");
                                stringBuffer.append(" or ");
                                a = jsonObject0.getString("parent_level");
                            } else if (jsonObject0.getString("" + jsonObject1.getTableName() + "_id").equals(jsonObject3.getString("parent_level"))) {
                                stringBuffer.append("" + jsonObject1.getTableName() + "_id = " + jsonObject3.getString("" + jsonObject1.getTableName() + "_id") + "");
                                stringBuffer.append(" or ");
                                l = "parent_level";
                                a = jsonObject0.getString("" + jsonObject1.getTableName() + "_id");
                            }
                        }

                    } else if (list.contains(jsonObject1.getTableName())) {
                        List<Map<String, Object>> mapList0 = dbServerJdbcTemplate.queryForList("select  * from  " + jsonObject1.getTableName() + " where " + jsonObject1.getTableName() + "_id" + " =" + jsonObject1.getId() + "");
                        JSONArray jsonArray3 = JSON.parseArray(JSON.toJSONString(mapList0));
                        JSONObject jsonObject3 = jsonArray3.getJSONObject(0);
                        stringBuffer.append("  " + mTable + "_id=" + jsonObject3.getString("" + jsonObject1.getTableName() + "_id") + "");
                        stringBuffer.append(" or ");
                    }

                }
                if (mapList5.size() > 0 || list.contains(jsonObject1.getTableName())) {
                    stringBuffer.delete(stringBuffer.toString().lastIndexOf("or"), stringBuffer.length());
                } else {
                    stringBuffer.delete(stringBuffer.toString().lastIndexOf("where"), stringBuffer.length());
                }

            }
        }
        List<Map<String, Object>> formDataList = dbServerJdbcTemplate.queryForList(stringBuffer.toString());
        fromDataList = JSONArray.parseArray(JSON.toJSONString(formDataList));
        return fromDataList;
    }

    public JSONArray findsPage(int formListId, Integer pageIndex, Integer pageSize) throws Exception {
        {
            ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
            HttpServletRequest request = attributes.getRequest();
            HttpSession session = request.getSession();
            UserInfo userInfo = (UserInfo) session.getAttribute(SystemEnum.SESSION_USER.getValue());
            JSONArray jsonArray = new JSONArray();
            JSONObject retuJson = new JSONObject();
            JdbcTemplate jdbcTemplate = msFactory.getJdbcTemplate();
            StringBuffer stringBuffer = new StringBuffer();
            List<Map<String, Object>> mapList = jdbcTemplate.queryForList("select * from form_list where formListId=" + formListId);
            JSONArray formList = JSONArray.parseArray(JSON.toJSONString(mapList));
            JSONObject jsonObject = formList.getJSONObject(0);
            String mTable = jsonObject.getString("cTableName");
            //	System.out.println("mTable:" + mTable);
            List<Map<String, Object>> tableList = jdbcTemplate.queryForList("select * from table_class where tableClassName='" + mTable + "'");
            JSONArray cols = JSON.parseArray(JSON.toJSONString(tableList));
            JSONObject jdbcJSON = cols.getJSONObject(0);
            Integer dbInfoId = jdbcJSON.getInteger("dbInfoId");
            JdbcTemplate dbServerJdbcTemplate = dbServer.getJdbcTemplate(jdbcJSON.getInteger("dbInfoId"));
            int mTableId = jdbcJSON.getInteger("tableClassId");
            List<Map<String, Object>> sTableList = jdbcTemplate.queryForList("select * from table_class where mTableId=" + mTableId);
            List<CreateTable> createTables = createTableDao.selectList(new QueryWrapper<CreateTable>() {{
                this.eq("tableName", mTable);
            }});
            stringBuffer.append("select ");
            stringBuffer.append(mTable + "." + mTable + "_id as " + mTable + "_id,");
            for (int b = 0; b < createTables.size(); b++) {
                CreateTable createTable = createTables.get(b);
                FormLtem formltems = formItemsDao.selectOne(new QueryWrapper<FormLtem>() {{
                    this.eq("cTableField", createTable.getField());
                    this.eq("cTableName", mTable);
                }});
                if ("datetime".equals(createTable.getType())) {
                    stringBuffer.append("( select convert(varchar(100)," + createTable.getField() + ",120)) as " + createTable.getField() + ",");
                } else if (formltems != null) {
                    if (!"{}".equals(formltems.getRef())) {
                        JSONObject refJSON = JSON.parseObject(formltems.getRef());
                        JSONObject ref = JSON.parseObject(refJSON.getString("ref"));
                        stringBuffer.append("(select " + ref.getString("cTableField") + " from " + ref.getString("cTableName") + " where "
                                + ref.getString("cTableName") + "_id=" + mTable + "." + formltems.getCTableField() + " ) as " + createTable.getField() + ",");
                    } else {
                        stringBuffer.append(mTable + "." + createTable.getField() + " as " + createTable.getField() + ",");
                    }
                } else if (createTable.getField() == "parent_level") {
                    stringBuffer.append("(select  " + mTable + "_name from " + mTable + " where " + mTable + "_id =" + mTable + ")");
                }

            }
            stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
            stringBuffer.append(" from " + mTable);
            JSONArray fromDataList = new JSONArray();
//			if (jsonObject.getString("formTypes").equals("form")) {
//				if (userInfo.getUserName().equals("admin")) {
//					System.out.println("sddddddddddddddddddddd" + stringBuffer);
//					List<Map<String, Object>> formDataList = dbServerJdbcTemplate.queryForList(stringBuffer.toString());
//
//				}
//				stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
//				stringBuffer.append(" from " + mTable);
            if (jsonObject.getString("formTypes").equals("form")) {
                if (userInfo.getUserName().equals("admin")) {
                    //			System.out.println("sssss"+stringBuffer);
                    List<Map<String, Object>> formDataList = dbServerJdbcTemplate.queryForList(stringBuffer.toString());
                    fromDataList = JSONArray.parseArray(JSON.toJSONString(formDataList));

                } else {
                    stringBuffer.append(" where ");
                    List<UserinfoJurisdiction> userNumber = userinfo_jurisdictionDao.selectList(new QueryWrapper<UserinfoJurisdiction>() {{
                        this.eq("userNumber", userInfo.getUserName());
                    }});
                    for (UserinfoJurisdiction userinfoJurisdiction : userNumber) {
                        stringBuffer.append(" " + mTable + "_single_person='" + userinfoJurisdiction.getUserjurisdiction() + "'");
                        stringBuffer.append(" or ");
                    }
                    stringBuffer.append(" " + mTable + "_single_person='" + userInfo.getUserName() + "'");
                }

            } else {

                List<DataPermissions> json = dataPermissionsDao.selectList(new QueryWrapper<DataPermissions>() {{
                    this.eq("data_userNumber", userInfo.getUserName());
                }});
                List<Map<String, Object>> mapListu = jdbcTemplate.queryForList("select cTreeStruct from form_list where formListId=" + formListId + "");
                JSONArray jsonArrayu = JSON.parseArray(JSON.toJSONString(mapListu));
                JSONObject jsonObjectu = jsonArrayu.getJSONObject(0);
                String table = jsonObjectu.getString("cTreeStruct");
                String tables[] = table.replace("[", "").replace("]", "").split(",");
                List list = new ArrayList();
                for (int s = 0; s < tables.length; s++) {
                    list.add(tables[s]);
                }
                if (json.size() > 0) {
                    stringBuffer.append(" where ");
                    List<Map<String, Object>> mapList5 = null;
                    DataPermissions jsonObject1 = null;
                    for (int i = 0; i < json.size(); i++) {
                        jsonObject1 = json.get(i);
                        mapList5 = dbServerJdbcTemplate.queryForList("SELECT * FROM syscolumns WHERE id=OBJECT_ID('" + jsonObject1.getTableName() + "') AND name='" + mTable + "_id'");
                        List<Map<String, Object>> mapList1 = dbServerJdbcTemplate.queryForList("select  * from  " + jsonObject1.getTableName() + " where " + jsonObject1.getTableName() + "_id =" + jsonObject1.getId() + "");
                        JSONArray jsonArray1 = JSON.parseArray(JSON.toJSONString(mapList1));
                        JSONObject jsonObject2 = jsonArray1.getJSONObject(0);
                        List<Map<String, Object>> mapList2 = dbServerJdbcTemplate.queryForList("select *  from  " + jsonObject1.getTableName() + "");
                        JSONArray jsonArray2 = JSON.parseArray(JSON.toJSONString(mapList2));
                        String a = jsonObject1.getId();
                        String b = jsonObject1.getTableName() + "_id";
                        String l = jsonObject1.getTableName() + "_id";
                        String g = jsonObject1.getId();
                        mapList5 = dbServerJdbcTemplate.queryForList("SELECT * FROM syscolumns WHERE id=OBJECT_ID('" + mTable + "') AND name='" + jsonObject1.getTableName() + "_id'");
                        if (mapList5.size() > 0) {
                            stringBuffer.append("" + jsonObject1.getTableName() + "_id = " + jsonObject2.getString("" + jsonObject1.getTableName() + "_id"));
                            stringBuffer.append(" or ");
                            for (int c = 0; c < jsonArray2.size(); c++) {
                                JSONObject jsonObject3 = jsonArray2.getJSONObject(c);
                                List<Map<String, Object>> mapList0 = dbServerJdbcTemplate.queryForList("select  * from  " + jsonObject1.getTableName() + " where " + jsonObject1.getTableName() + "_id" + " =" + jsonObject1.getId() + "");
                                JSONArray jsonArray0 = JSON.parseArray(JSON.toJSONString(mapList0));
                                JSONObject jsonObject0 = jsonArray0.getJSONObject(0);
                                if (jsonObject0.getString("parent_level").equals(jsonObject3.getString("" + jsonObject1.getTableName() + "_id"))) {

                                    stringBuffer.append(" " + jsonObject1.getTableName() + "_id =" + jsonObject3.getString("" + jsonObject1.getTableName() + "_id") + "");
                                    stringBuffer.append(" or ");
//								a = jsonObject0.getString("parent_level");
                                } else if (jsonObject0.getString("" + jsonObject1.getTableName() + "_id").equals(jsonObject3.getString("parent_level"))) {
                                    stringBuffer.append("" + jsonObject1.getTableName() + "_id = " + jsonObject3.getString("" + jsonObject1.getTableName() + "_id") + "");
                                    stringBuffer.append(" or ");
//								l = "parent_level";
//								a = jsonObject0.getString("" + jsonObject1.getTableName() + "_id");
                                }
                            }
                            for (int c = 0; c < jsonArray2.size(); c++) {
                                JSONObject jsonObject3 = jsonArray2.getJSONObject(c);
                                List<Map<String, Object>> mapList0 = dbServerJdbcTemplate.queryForList("select  * from  " + jsonObject1.getTableName() + " where " + l + "" + " =" + a + "");
                                JSONArray jsonArray0 = JSON.parseArray(JSON.toJSONString(mapList0));
                                JSONObject jsonObject0 = jsonArray0.getJSONObject(0);
                                if (jsonObject0.getString("parent_level").equals(jsonObject3.getString("" + jsonObject1.getTableName() + "_id"))) {

                                    stringBuffer.append(" " + jsonObject1.getTableName() + "_id =" + jsonObject3.getString("" + jsonObject1.getTableName() + "_id") + "");
                                    stringBuffer.append(" or ");
                                    a = jsonObject0.getString("parent_level");
                                } else if (jsonObject0.getString("" + jsonObject1.getTableName() + "_id").equals(jsonObject3.getString("parent_level"))) {
                                    stringBuffer.append("" + jsonObject1.getTableName() + "_id = " + jsonObject3.getString("" + jsonObject1.getTableName() + "_id") + "");
                                    stringBuffer.append(" or ");
                                    l = "parent_level";
                                    a = jsonObject0.getString("" + jsonObject1.getTableName() + "_id");
                                }
                            }

                        } else if (list.contains(jsonObject1.getTableName())) {
                            List<Map<String, Object>> mapList0 = dbServerJdbcTemplate.queryForList("select  * from  " + jsonObject1.getTableName() + " where " + jsonObject1.getTableName() + "_id" + " =" + jsonObject1.getId() + "");
                            JSONArray jsonArray3 = JSON.parseArray(JSON.toJSONString(mapList0));
                            JSONObject jsonObject3 = jsonArray3.getJSONObject(0);
                            stringBuffer.append("  " + mTable + "_id=" + jsonObject3.getString("" + jsonObject1.getTableName() + "_id") + "");
                            stringBuffer.append(" or ");
                        }

                    }
                    if (mapList5.size() > 0 || list.contains(jsonObject1.getTableName())) {
                        stringBuffer.delete(stringBuffer.toString().lastIndexOf("or"), stringBuffer.length());
                    } else {
                        stringBuffer.delete(stringBuffer.toString().lastIndexOf("where"), stringBuffer.length());
                    }

                }
            }
            StringBuffer newstringBuffer = new StringBuffer();
            if (!pageIndex.equals(null)) {
                newstringBuffer.append("SELECT * FROM ( SELECT  * , ROW_NUMBER() OVER(ORDER BY A." + mTable + "_id  " + " ) AS ROWNUM FROM (");
                newstringBuffer.append(stringBuffer);
                int startNum = (pageIndex - 1) * pageSize;
                newstringBuffer.append(") AS A )AS B  WHERE ROWNUM > " + startNum + " AND ROWNUM <= " + pageIndex * pageSize);
            }
            List<Map<String, Object>> formDataList = dbServerJdbcTemplate.queryForList(newstringBuffer.toString());
            fromDataList = JSONArray.parseArray(JSON.toJSONString(formDataList));
            return fromDataList;
        }
    }

    public String findCode(String str) throws Exception {
        JSONObject jsonObject = JSON.parseObject(str);
        //		System.out.println("jsonObject:" + jsonObject);

//		String cTableName = jsonObject.getString("");
        String returnFile = jsonObject.getString("returnFileld");
        String[] c = returnFile.split("_");
        String cTableName = c[0];
        String fileldV = jsonObject.getString("fileldV");
        List<TableClass> tableClasses = tableClassDao.selectList(new QueryWrapper<TableClass>() {{
            this.eq("tableClassName", cTableName);
        }});
        //	System.out.println("cTableName:" + cTableName);
        //	System.out.println("tableClasses:" + tableClasses);
        JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(tableClasses.get(0).getDbInfoId());

        String sql = "select top 1 " + returnFile + " from " + cTableName + " order by " + cTableName + "_id desc";
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
        String newCode = "";
        int a = Integer.parseInt(fileldV.substring(fileldV.length() - 1, fileldV.length()));
        if (mapList.size() > 0) {

            //		System.out.println("a:" + a);

            Map<String, Object> map = mapList.get(0);
            String tableName = cTableName + "_code";
            String code = map.get(tableName).toString();
            String codeL = code.substring(code.length() - a, code.length());
            int nCode = Integer.parseInt(codeL) + 1;
            //		System.out.println("newCode:" + nCode);
            newCode = String.format("%0" + a + "d", nCode);
            //		System.out.println("newCode:" + newCode);
        } else {
            newCode = String.format("%0" + a + "d", 1);
        }
        JSONObject returnJosn = new JSONObject();
        returnJosn.put("returnFile", returnFile);
        returnJosn.put("valu", newCode);

        return returnJosn.toString();
    }

    public String findSum(String str) throws Exception {
        String returnFiled = "";
        try {
            JSONObject jsonObject = JSON.parseObject(str);
            //		System.out.println("jsonObject=======================" + jsonObject);
            //<

            //	通过备用金号(fileldValue1)查询备用金申请表
            String fileldValue1 = jsonObject.getString("fileldValue");
            List<TableClass> applicationFund = tableClassDao.selectList(new QueryWrapper<TableClass>() {{
                this.eq("tableClassName", "ApplicationFund");
            }});
            JdbcTemplate jdbcTemplate3 = dbServer.getJdbcTemplate(applicationFund.get(0).getDbInfoId());

            String querySql = "select * from ApplicationFund where ApplicationFund_code = '" + fileldValue1 + "'";
            List<Map<String, Object>> maps = jdbcTemplate3.queryForList(querySql);

            Map<String, Object> oneMap = maps.get(0);

            //通过ApplicationFund(备用金申请表)的id去srpayment(备用金付款表)查询实付金额
            int applicationFund_id = (int) oneMap.get("ApplicationFund_id");

            String applicationFundSql = "select srpayment_xgry_0005 from srpayment where srpayment_xgry_0001 = '" + applicationFund_id + "'";
            //		System.out.println("applicationFundSql==========" + applicationFundSql);
            //query:  备用金付款表目前按照1(备用金申请单):1(备用金付款单)  后期query可为多条数据集合
            List<Map<String, Object>> query = jdbcTemplate3.queryForList(applicationFundSql);
            if (query.size() > 0) {
                Map<String, Object> map3 = query.get(0);

                String sqltwo = "update ApplicationFund set ApplicationFund_xgry_0010 = '" + map3.get("srpayment_xgry_0005") + "' where ApplicationFund_code = '" + fileldValue1 + "'";
                jdbcTemplate3.update(sqltwo);
            } else {
                String sqltwo = "update ApplicationFund set ApplicationFund_xgry_0010 = '" + 0.00 + "' where ApplicationFund_code = '" + fileldValue1 + "'";
                jdbcTemplate3.update(sqltwo);
            }


            String sqlone = "select (ApplicationFund_xgry_0003-ApplicationFund_xgry_0014) ApplicationFund_xgry_0000 from ApplicationFund where ApplicationFund_code = '" + fileldValue1 + "'";
            List<Map<String, Object>> mapList3 = jdbcTemplate3.queryForList(sqlone);
            Map<String, Object> map2 = mapList3.get(0);

            if (map2.size() > 0) {
                if (map2.get("ApplicationFund_xgry_0000") != null) {
                    String applicationFundSql1 = "update ApplicationFund set ApplicationFund_xgry_0015 = '" + map2.get("ApplicationFund_xgry_0000") + "' where ApplicationFund_code = '" + fileldValue1 + "'";
                    jdbcTemplate3.update(applicationFundSql1);
                }
            } else {
                String applicationFundSql1 = "update ApplicationFund set ApplicationFund_xgry_0015 = '" + 0.00 + "' where ApplicationFund_code = '" + fileldValue1 + "'";
                jdbcTemplate3.update(applicationFundSql1);
            }


            if (jsonObject.get("returnFileld").equals("DetailsOffsetReserveGoods_xgry_0005")) {
                String detailsOffsetReserveGoods_xgry_0008 = jsonObject.getString("sumFileld");
                String[] s = detailsOffsetReserveGoods_xgry_0008.split("_");
                String detailsOffsetReserveGoods = s[0];
                String fileldValue = jsonObject.getString("fileldValue");
                String returnFileld = jsonObject.getString("returnFileld");
                String whereFileld = jsonObject.getString("whereFileld");
                List<TableClass> byTableName = tableClassDao.selectList(new QueryWrapper<TableClass>() {{
                    this.eq("tableClassName", (detailsOffsetReserveGoods));
                }});
                tableClassDao.selectList(new QueryWrapper<TableClass>() {{
                    this.eq("tableClassName", detailsOffsetReserveGoods);
                }});
                JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(byTableName.get(0).getDbInfoId());
                String sql = "select sum(" + detailsOffsetReserveGoods_xgry_0008 + ") as " + returnFileld + " from " + detailsOffsetReserveGoods + " where detailsOffsetReserveGoods_xgry_0001 ='" + fileldValue + "'and DetailsOffsetReserveGoods_state = 1";
                //			System.out.println("sql^^^^^^^^^^^^^^^^^^^^^^" + sql);

                String sql1 = "select sum(DetailsOfReserveFund_xgry_0008) as DetailsOffsetReserveGoods_xgry_0005 from DetailsOfReserveFund where DetailsOfReserveFund_xgry_0001 = '" + fileldValue + "'and DetailsOfReserveFund_state = 1";

                List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
                List<Map<String, Object>> mapList1 = jdbcTemplate.queryForList(sql1);

                BigDecimal s1 = null;
                BigDecimal s2 = null;
                if (mapList.size() > 0) {
                    Map<String, Object> map = mapList.get(0);
                    //			System.out.println("mapmapmapmap//////////////////" + map);
                    Object detailsOffsetReserveGoods_xgry_0005 = map.get("DetailsOffsetReserveGoods_xgry_0005");
                    s1 = (BigDecimal) detailsOffsetReserveGoods_xgry_0005;
                }

                if (mapList1.size() > 0) {
                    Map<String, Object> map1 = mapList1.get(0);
                    //			System.out.println("mapmapmapmap//////////////////" + map1);
                    Object detailsOffsetReserveGoods_xgry_0005 = map1.get("DetailsOffsetReserveGoods_xgry_0005");
                    s2 = (BigDecimal) detailsOffsetReserveGoods_xgry_0005;
                }

                if (s1 != null) {
                    returnFiled = s1.toString();
                }
                if (s2 != null) {
                    returnFiled = s2.toString();
                }
                if (s1 != null && s2 != null) {
                    returnFiled = s1.add(s2).toString();
                }
                if (s1 == null && s2 == null) {
                    returnFiled = "0.00";
                }
                return returnFiled;
            }


            // 以抵金额
            if (jsonObject.get("returnFileld").equals("DetailsOffsetReserveGoods_xgry_0004")) {
                //> 获取DetailsOffsetReserveGoods冲抵备用金表名
                String DetailsOffset = jsonObject.getString("returnFileld");
                String fileldValue = jsonObject.getString("fileldValue");
                String[] DetailsOffset1 = DetailsOffset.split("_");
                String cTableName1 = DetailsOffset1[0];
                //	System.out.println("cTableName1>>>>>>>>>>>>>>>>>>>>>:" + cTableName1);

                List<TableClass> tableClassList = tableClassDao.selectList(new QueryWrapper<TableClass>() {{
                    this.eq("tableClassName", cTableName1);
                }});
                JdbcTemplate jdbcTemplate1 = dbServer.getJdbcTemplate(tableClassList.get(0).getDbInfoId());
                String sql1 = "select sum(DetailsOffsetReserveGoods_xgry_0008) as DetailsOffsetReserveGoods_xgry_0008 from DetailsOffsetReserveGoods where DetailsOffsetReserveGoods_xgry_0001 = '" + fileldValue + "' and DetailsOffsetReserveGoods_state = 3";
                //		System.out.println("sql1>>>>>>>>>>>>>>>>>>" + sql1);
                List<Map<String, Object>> mapList1 = jdbcTemplate1.queryForList(sql1);
                //		System.out.println("mapList1>>>>>>>>>>>>>>>>>>" + mapList1);

                List<TableClass> detailsOfReserveFund = tableClassDao.selectList(new QueryWrapper<TableClass>() {{
                    this.eq("tableClassName", "DetailsOfReserveFund");
                }});
                JdbcTemplate jdbcTemplate2 = dbServer.getJdbcTemplate(detailsOfReserveFund.get(0).getDbInfoId());
                String sql2 = "select sum(DetailsOfReserveFund_xgry_0008) as DetailsOfReserveFund_xgry_0008 from DetailsOfReserveFund where DetailsOfReserveFund_xgry_0001 = '" + fileldValue + "' and DetailsOfReserveFund_state = 3";
                //	System.out.println("sql2>>>>>>>>>>>>>>>>>>" + sql2);
                List<Map<String, Object>> mapList2 = jdbcTemplate2.queryForList(sql2);
                //	System.out.println("mapList2>>>>>>>>>>>>>>>>>>" + mapList2);

                BigDecimal num = null;
                BigDecimal num1 = null;
                BigDecimal num2 = null;

                if (mapList1.size() > 0) {
                    Map<String, Object> map = mapList1.get(0);
                    Object nu = map.get("DetailsOffsetReserveGoods_xgry_0008");
                    if (nu != null) {
                        num = (BigDecimal) nu;
                    }
                }

                if (mapList2.size() > 0) {
                    Map<String, Object> map = mapList2.get(0);
                    Object nu = map.get("DetailsOfReserveFund_xgry_0008");
                    if (nu != null) {
                        num1 = (BigDecimal) nu;
                    }
                }

                if (num != null) {
                    num2 = num;
                }

                if (num1 != null) {
                    num2 = num1;
                }

                if (num != null && num1 != null) {
                    num2 = num.add(num1);
                }

                if (num2 == null) {
                    String applicationFundSql1 = "update ApplicationFund set ApplicationFund_xgry_0011 = '" + 0.00 + "' where ApplicationFund_code = '" + fileldValue1 + "'";
                    jdbcTemplate3.update(applicationFundSql1);
                    returnFiled = "0.00";
                } else {
                    String applicationFundSql1 = "update ApplicationFund set ApplicationFund_xgry_0011 = '" + num2 + "' where ApplicationFund_code = '" + fileldValue1 + "'";
                    jdbcTemplate3.update(applicationFundSql1);
                    returnFiled = num2.toString();
                }
                //<
            }
            //>

            //	已归还金额
            else {
                ////str:{"sumFileld":"StandbyListDetails_xgry_0006","fileldValue":"2019090330015","returnFileld":"DetailsOffsetReserveGoods_xgry_0006","whereFileld":"StandbyListDetails_xgry_0001","fileld":"DetailsOffsetReserveGoods","cMath":"[DetailsOffsetReserveGoods_xgry_0006]=findTableSum([StandbyListDetails_xgry_0006]where[StandbyListDetails_xgry_0001]=[DetailsOffsetReserveGoods_xgry_0001])"}
                String sumFileld = jsonObject.getString("sumFileld");
                String[] s = sumFileld.split("_");
                String cTableName = s[0];
                //	System.out.println("cTableName:" + cTableName);
                List<TableClass> tableClasses = tableClassDao.selectList(new QueryWrapper<TableClass>() {{
                    this.eq("tableClassName", (cTableName));
                }});
                JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(tableClasses.get(0).getDbInfoId());
                String returnFileld = jsonObject.getString("returnFileld");
                String whereFiled = jsonObject.getString("whereFileld");
                String fileValue = jsonObject.getString("fileldValue");
                String sql = "select sum(" + sumFileld + ") as " + returnFileld + " from " + cTableName + " where " + whereFiled + "='" + fileValue + "'";
                List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
                //		System.out.println("findSum : " + sql);

                //	System.out.println("mapList.size():" + mapList.size());
                if (mapList.size() > 0) {
                    Map<String, Object> map = mapList.get(0);
                    //		System.out.println("map:" + map);

                    if (map.get(returnFileld) != null) {
                        String applicationFundSql1 = "update ApplicationFund set ApplicationFund_xgry_0014 = '" + map.get(returnFileld) + "' where ApplicationFund_code = '" + fileldValue1 + "'";
                        jdbcTemplate3.update(applicationFundSql1);
                        returnFiled = map.get(returnFileld).toString();
                    } else {
                        String applicationFundSql1 = "update ApplicationFund set ApplicationFund_xgry_0014 = '" + 0.00 + "' where ApplicationFund_code = '" + fileldValue1 + "'";
                        jdbcTemplate3.update(applicationFundSql1);
                        returnFiled = "0.00";
                    }

                    //		System.out.println("returnFiled>>>>>>>>>>>>>>>>>>>" + returnFiled);
                }

            }

        } catch (Exception e) {

        }
        return returnFiled;
    }

    public String findFormState(String str) throws Exception {
        JSONObject jsonObject = JSON.parseObject(str);
        String file1 = jsonObject.getString("a");
        String[] file = file1.split("_");
        String cTableName = file[0];
        List<TableClass> tableClass = tableClassDao.selectList(new QueryWrapper<TableClass>() {{
            this.eq("tableClassName", (cTableName));
        }});
        JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(tableClass.get(0).getDbInfoId());
        String b = jsonObject.getString("b");
        String c = jsonObject.getString("c");
        String d = jsonObject.getString("d");
        String f = jsonObject.getString("f");
        String g = jsonObject.getString("g");
        String h = jsonObject.getString("h");
        String valu = jsonObject.getString("fileldValue");
        int retuB = 0;
        int retuC = 0;
        if (b == "已提交") {
            retuB = 1;
        }
        if (d == "未终审") {
            retuC = 3;
        }
        String sql = "select sum(" + f + ") as " + g + " from " + cTableName + " where " + file1 + "<=" + retuB + " and " + c + ">=" + retuC + " and " + h + "='" + valu + "'";
        String retuMap = "";
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
        if (mapList.size() > 0) {
            Map<String, Object> map = mapList.get(0);
            retuMap = map.get(f).toString();
        }
        return retuMap;
    }

//	@Override
//	public String checkday(String bengin, String end) throws Exception {
//		long day=0;
//		SimpleDateFormat format=new SimpleDateFormat("yyy-MM-dd");
//		Date begintime;
//		Date endtime;
//
//		    begintime= (Date) format.parse(bengin);
//		    endtime= (Date) format.parse(end);
//		    day=(endtime.getTime()-begintime.getTime())/(24*60*60*1000);
//
//
//
//		return String.valueOf(day);
//	}


    /**
     * 通用修改方法
     */
    public String updateBaseTwo(String tableName, Map<String, Object> maps, String tableId) {
        // TODO Auto-generated method stub

        StringBuffer stringBuffer = new StringBuffer();

        stringBuffer.append("update " + tableName + " set ");

        for (Entry<String, Object> map : maps.entrySet()) {
            stringBuffer.append(map.getKey() + "='" + map.getValue() + "',");
        }

        stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
        stringBuffer.append(" where  " + tableName + "_id='" + tableId + "'");

        return stringBuffer.toString();
    }


    /**
     * 通用删除
     */
    public String deleteBase(String tableName, Map<String, Object> maps) {

        StringBuffer sBuffer = new StringBuffer();
        sBuffer.append("delete from " + tableName + " where ");
        for (Entry<String, Object> map : maps.entrySet()) {
            sBuffer.append(map.getKey() + " = " + map.getValue());
        }
        return sBuffer.toString();
    }

    /**
     * 通用删除
     */
    public String deleteBaseForm(String tableName, String tableId) {
        //	System.out.println("tableName:" + tableName + "tableId:" + tableId);
        StringBuffer sBuffer = new StringBuffer();
        sBuffer.append("delete from " + tableName + " where id='" + tableId + "'");

        return sBuffer.toString();
    }

    /**
     * 通用删除
     */
    public String deleteBaseFormAndStable(String tableName, String mTableId) {

        StringBuffer sBuffer = new StringBuffer();
        sBuffer.append("delete from " + tableName + " where mTableId='" + mTableId + "'");

        return sBuffer.toString();
    }


    /**
     * 根据表明查询所有数据
     */
    public String findAll(String tableName, String maps) {
        // TODO Auto-generated method stub
        StringBuffer sBuffer = new StringBuffer();
        sBuffer.append("select " + maps + " from " + tableName);

        return sBuffer.toString();
    }

    /**
     * 通用修改方法
     */
    public String updateBase(String tableName, Map<String, Object> maps, String tableId) {
        // TODO Auto-generated method stub

        StringBuffer stringBuffer = new StringBuffer();

        stringBuffer.append("update " + tableName + " set ");

        for (Entry<String, Object> map : maps.entrySet()) {
            stringBuffer.append(map.getKey() + "='" + map.getValue() + "',");
        }

        stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
        stringBuffer.append(" where id='" + tableId + "'");

        return stringBuffer.toString();
    }


    //	@Override
    public String selected() {
        List sql = null;
        try {
            JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(2);
            sql = jdbcTemplate.queryForList("select * from personnelfies");


        } catch (Exception e) {
            e.printStackTrace();
        }
        return sql.toString();
    }

    /**
     * 反向添加user—info表
     * @param jsonArray
     * @throws Exception
     */
//	@Override
//	public void insertuser(JSONArray jsonArray) throws Exception {
//		 JSONObject jsonObject=jsonArray.getJSONObject(0);
//		 String tablename=jsonObject.getString("cTableName");
//		 JSONObject jsonObject1=jsonObject.getJSONObject("obj");
//		 String uname=jsonObject1.getString("PersonnelFiles_name");
//		 String uid=jsonObject1.getString("PersonnelFiles_OperatorCode");
//		 String email=jsonObject1.getString("PersonnelFiles_Email");
//		 String a= "12344adaw";
//		 String passwod=MD5Util.MD5(a);
//		 String code=jsonObject1.getString("PersonnelFiles_code");
//		JdbcTemplate jdbcTemplate = msFactory.getJdbcTemplate();
//		List<Map<String, Object>> map=jdbcTemplate.queryForList("select * from  table_class where tableClassName='"+tablename+"'");
//		JSONArray jsonArray1=JSON.parseArray(JSON.toJSONString(map));
//		JSONObject jsonObject2=jsonArray1.getJSONObject(0);
//		JdbcTemplate jdbcTemplate1 = dbServer.getJdbcTemplate(jsonObject2.getInteger("dbInfoId"));
//		List<Map<String, Object>> maps=jdbcTemplate1.queryForList("select * from "+tablename+" where PersonnelFiles_OperatorCode="+uid+"");
//		JSONArray jsonArray2=JSON.parseArray(JSON.toJSONString(maps));
//		JSONObject jsonObject3=jsonArray2.getJSONObject(0);
//		String personid=jsonObject3.getString("PersonnelFiles_id");
//		jdbcTemplate.update("insert into user_info(uName,personnelId,userEmail,userState,password,userCode) values ('"+uname+"',"+personid+",'"+email+"',1,'"+passwod+"','"+code+"')");
//	}

    /**
     * 通用新增方法
     *
     * @param tableName 目标表名
     * @param maps      map参数。key为字段名称，value 为字段值
     */
    public String insertBase(String tableName, Map<String, Object> maps) {
        // TODO Auto-generated method stub

        StringBuffer sBuffer = new StringBuffer();

        sBuffer.append("insert into " + tableName + "(");

        for (Entry<String, Object> map : maps.entrySet()) {
            sBuffer.append(map.getKey() + ",");
        }
        sBuffer.delete(sBuffer.toString().lastIndexOf(","), sBuffer.length());
        sBuffer.append(") values (");

        for (Entry<String, Object> map : maps.entrySet()) {
            sBuffer.append("'" + map.getValue() + "'" + ",");
        }
        sBuffer.delete(sBuffer.toString().lastIndexOf(","), sBuffer.length());
        sBuffer.append(")");

        return sBuffer.toString();
    }

    /**
     * 通用新增返回主键方法
     *
     * @param tableName 目标表名
     * @param maps      map参数。key为字段名称，value 为字段值
     */
    public String insertBaseOutPut(String tableName, Map<String, Object> maps) {
        // TODO Auto-generated method stub

        StringBuffer sBuffer = new StringBuffer();

        sBuffer.append("insert into " + tableName + "(");

        for (Entry<String, Object> map : maps.entrySet()) {
            sBuffer.append(map.getKey() + ",");
        }
        sBuffer.delete(sBuffer.toString().lastIndexOf(","), sBuffer.length());
        sBuffer.append(") values (");

        for (Entry<String, Object> map : maps.entrySet()) {
            sBuffer.append("'" + map.getValue() + "'" + ",");
        }
        sBuffer.delete(sBuffer.toString().lastIndexOf(","), sBuffer.length());
        sBuffer.append(")");

        return sBuffer.toString();
    }


    /**
     * 建表语句
     *
     * @param tableName
     * @param createTables
     * @return
     */
    public String createTable(String tableName, List<CreateTable> createTables) {
        // TODO Auto-generated method stub
        StringBuffer sBuffer = new StringBuffer();
        sBuffer.append("create table " + tableName + " (id varchar(255) NOT NULL, formId int,processInstanceId varchar(255), processStatus int ,_id int NULL,_uid int NULL,status varchar(255),");

        for (CreateTable createTables2 : createTables) {
            sBuffer.append(createTables2.getField() + " ");
            sBuffer.append(createTables2.getType() + " ");

            if (createTables2.getNotKey() == "true") {
                sBuffer.append(" PRIMARY KEY ");
            }

            if (createTables2.getNotNull() == "true") {
                sBuffer.append(" not null ");
            } else if (createTables2.getNotNull() == "false") {
                sBuffer.append(" null ");
            }
            sBuffer.append(",");
        }
        sBuffer.delete(sBuffer.toString().lastIndexOf(","), sBuffer.length());
        sBuffer.append(")");
//		System.out.println(sBuffer.toString());

        return sBuffer.toString();
    }

    /**
     * 生成外键sql
     *
     * @param tableName
     * @param tableName2
     * @param filedName
     * @return
     */
    public String Createkey(String tableName, String tableName2, String filedName) {
        // TODO Auto-generated method stub
        String string = tableName + "_" + tableName2 + "_" + filedName;
        String sql = "ALTER TABLE " + tableName +
                "ADD NULL CONSTRAINT" + string + "FOREIGN KEY (" +
                filedName + ") REFERENCES" + tableName2 + "(" + filedName + ") ON DELETE NO ACTION ON UPDATE NO ACTION";

        return sql;
    }

    /**
     * 根据id查询对象的sql
     *
     * @param tableName
     * @param maps
     * @return
     */
    public String findById(String tableName, Map<String, String> maps) {
        // TODO Auto-generated method stub
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select * from " + tableName + " where ");

        String field = maps.get("key");
        CreateTable createTable = createTableDao.selectOne(new QueryWrapper<CreateTable>() {{
            this.eq("tableName", tableName);
            this.eq("field", field);
        }});
        ;
        createTableDao.selectOne(new QueryWrapper<CreateTable>() {{
            this.eq("tableName", tableName);
            this.eq("field", field);
        }});

        if (field != "int") {
            for (Entry<String, String> map : maps.entrySet()) {
                stringBuffer.append(map.getKey() + " = '" + map.getValue() + "'");
            }
        } else {
            for (Entry<String, String> map : maps.entrySet()) {
                stringBuffer.append(map.getKey() + " = " + Integer.parseInt(map.getValue()));
            }
        }
        //	System.out.println(stringBuffer.toString());
        return stringBuffer.toString();

    }

    /**
     * 根据StableId查询对象的sql
     *
     * @param tableName
     * @return
     */
    public String findByStableId(String tableName, String mTableId) {
        // TODO Auto-generated method stub
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select * from " + tableName + " where mTableId='" + mTableId + "'");
        return stringBuffer.toString();

    }

    /**
     * 根据MtableId查询对象的sql
     *
     * @param tableName
     * @return
     */
    public String findByMtableId(String tableName, String id) {
        // TODO Auto-generated method stub
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select * from " + tableName + " where id='" + id + "'");
        return stringBuffer.toString();

    }


    public String findAll1(String tableName) {
        // TODO Auto-generated method stub
        StringBuffer sBuffer = new StringBuffer();
        sBuffer.append("select *");

        sBuffer.append(" from " + tableName);

        return sBuffer.toString();

    }

    public String multiTable(String tableName, Map<String, String> fileldMap, List<TableField> tableFileId) {
        // TODO Auto-generated method stub

        StringBuffer stringBuffer = new StringBuffer();

        stringBuffer.append("select ");
        for (Entry<String, String> map : fileldMap.entrySet()) {
            String[] tableNameKey = map.getKey().split("-");
//			System.out.println("tableNameKey:" + tableNameKey[0]);
            String[] tableNameValue = map.getValue().split("-");
//			+ " as " + map.getKey() +
//			if (tableNameValue.length > 1) {
//				stringBuffer.append(tableNameValue[0]+ "." + map.getValue() +  ",");
//			}else {
            stringBuffer.append(tableNameKey[0] + "." + map.getValue() + " as " + tableNameKey[0] + "_" + map.getValue() + ",");
//			}

        }
        stringBuffer.append(tableName + ".id as id,");
        stringBuffer.append(tableName + ".name as name,");
        stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
        stringBuffer.append(" from " + tableName);
        if (tableFileId.size() > 0) {
            for (int i = 0; i < tableFileId.size(); i++) {
                stringBuffer.append(" left join " + tableFileId.get(i).getTableField() + " " + tableFileId.get(i).getTableField() + " on " + tableName + ".id=" + tableFileId.get(i).getTableField() + ".mTableId");
            }
        }

        return stringBuffer.toString();
    }

    public String findByMtableIdDaoStable(String sTableName, String mTableId) {

        String sql = "select * from " + sTableName + " where mTableId='" + mTableId + "'";

        return sql;
    }

    //关联数据权限
    public String dbSourceAndRole(String tableName, String id, List<TableField> tableFileId) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select * from " + tableName);
        if (tableFileId.size() > 0) {
            for (int i = 0; i < tableFileId.size(); i++) {
                stringBuffer.append(" left join " + tableFileId.get(i).getTableField() + " on " + tableName + ".id=" + tableFileId.get(i).getTableField() + ".mTableId" + " where " + tableFileId.get(i).getTableField() + ".id = " + id);
            }
        }
        return stringBuffer.toString();
    }


    /**
     * 流程关联数据查询
     */
    public String TechnologicalTable(String tableName, List<TableField> tableFileId, String processInstanceId) {
        // TODO Auto-generated method stub

        StringBuffer stringBuffer = new StringBuffer();

        stringBuffer.append("select * from " + tableName);

        for (int i = 0; i < tableFileId.size(); i++) {

            stringBuffer.append(" left join " + tableFileId.get(i).getTableField() + " on " +
                    tableName + ".formId=" + tableFileId.get(i).getTableField() + ".formId");

        }
        stringBuffer.append(" where " + tableName + ".processInstanceId ='" + processInstanceId + "'");
        return stringBuffer.toString();
    }

    public String FormByMtableId(String mTableName, List<String> sTable, String mTableId) {
        if (sTable == null) {
            StringBuffer stringBuffer = new StringBuffer();

            stringBuffer.append("select * from " + mTableName + " where id='" + mTableId + "'");
            //		System.out.println(stringBuffer.toString());
            return stringBuffer.toString();
        } else {
            StringBuffer stringBuffer = new StringBuffer();
            stringBuffer.append("select * from " + mTableName);
            //	System.out.println(sTable);
            for (int i = 0; i < sTable.size(); i++) {
                stringBuffer.append(" left join " + sTable.get(i) + " on " + mTableName + ".id=" + sTable.get(i) + ".mTableId");
            }
            stringBuffer.append(" where " + mTableName + ".id='" + mTableId + "'");

            return stringBuffer.toString();
        }
    }

    /**
     * 根据id使用in关键字查询数据集合
     *
     * @param cTableName
     * @param map
     * @return 返回sql
     */
    public String objectFindByIdin(String cTableName, String map) {

        StringBuffer findByIdinSql = new StringBuffer();

        findByIdinSql.append("select * from " + cTableName);

        findByIdinSql.append(" where id in (" + map + ")");

        return findByIdinSql.toString();
    }

    /**
     * 数据级联sql
     */
    public List<Map<String, Object>> cascade(String returnFileld, String cTableName, String fileld, String fileldValue) throws Exception {
        // TODO Auto-generated method stub
        StringBuffer cascadeSql = new StringBuffer();
        TableClass tableClass = tableClassDao.selectOne(new QueryWrapper<TableClass>() {{
            this.eq("tableClassName", cTableName);
        }});
        ;
        tableClassDao.selectOne(new QueryWrapper<TableClass>() {{
            this.eq("tableClassName", cTableName);
        }});
        JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(tableClass.getDbInfoId());
        cascadeSql.append("select " + returnFileld + " from " + cTableName + " where ");

        CreateTable createTable = createTableDao.selectOne(new QueryWrapper<CreateTable>() {{
            this.eq("tableName", cTableName);
            this.eq("field", fileld);
        }});
        ;

        if (createTable.getType() != "int") {
            cascadeSql.append(fileld + " = '" + fileldValue + "'");
        } else {
            cascadeSql.append(fileld + " = " + fileldValue);
        }
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(cascadeSql.toString());
        return mapList;
    }

    /**
     * 上一张单据数据
     *
     * @param cTableName
     * @param fileValue
     * @return
     */
    public String lastOne(String cTableName, String fileValue) {

        String lastOneSQL = "select * from " + cTableName + " where id < '" + fileValue + "'";

        return lastOneSQL;
    }

    /**
     * 下一张单据
     *
     * @param cTableName
     * @param fileValue
     * @return
     */
    public String nextPiece(String cTableName, String fileValue) {

        String nextPieceSQL = "select * from " + cTableName + " where id > '" + fileValue + "'";

        return nextPieceSQL;
    }

    /**
     * 根据条件查询数据
     *
     * @param cTableName
     * @param tableField
     * @param tableValue
     * @return
     */
    public String selectValue(String cTableName, String tableField, String tableValue) throws Exception {
        StringBuffer sBuffer = new StringBuffer();
        List<CreateTable> createTables = createTableDao.selectList(new QueryWrapper<CreateTable>() {{
            this.eq("tableName", cTableName);
        }});
        ;
        createTableDao.selectList(new QueryWrapper<CreateTable>() {{
            this.eq("tableName", cTableName);
        }});

        sBuffer.append(" select ");
        for (int i = 0; i < createTables.size(); i++) {
            String field = createTables.get(i).getField();
            FormLtem formltems = formItemsDao.selectOne(new QueryWrapper<FormLtem>() {{
                this.eq("cTableField", field);
                this.eq("cTableField", cTableName);

            }});
            if (formltems != null && !"{}".equals(formltems.getRef())) {
                JSONObject refJSONObject = JSON.parseObject(formltems.getRef());
                JSONObject ref = JSONObject.parseObject(refJSONObject.getString("ref"));
                sBuffer.append("(select " + ref.getString("cTableField") + " from " + ref.getString("cTableName") + " where " + ref.getString("cTableName") + "_id=" + cTableName + "." + formltems.getCTableField() + ")as " + formltems.getCTableField() + ",");
            } else if ("datetime".equals(createTables.get(i).getType())) {
                sBuffer.append("( select convert(varchar(100)," + createTables.get(i).getField() + ",120)) as " + createTables.get(i).getField() + ",");
            } else {
                sBuffer.append(createTables.get(i).getField() + " as " + createTables.get(i).getField() + ",");
            }

        }

        sBuffer.append(cTableName + "." + cTableName + "_id as " + cTableName + "_id,");
        sBuffer.delete(sBuffer.toString().lastIndexOf(","), sBuffer.length());
        sBuffer.append(" from " + cTableName + " where " + tableField + "='" + tableValue + "'");

        String table = tableField.replace("_id", "");
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletRequest request = attributes.getRequest();
        HttpSession session = request.getSession();
        UserInfo userInfo = (UserInfo) session.getAttribute(SystemEnum.SESSION_USER.getValue());
        JdbcTemplate jdbcTemplate = msFactory.getJdbcTemplate();
        List<Map<String, Object>> mapList3 = jdbcTemplate.queryForList("select * from table_class where tableClassName='" + cTableName + "'");
        JSONArray jsonArray22 = JSON.parseArray(JSON.toJSONString(mapList3));
        JSONObject jsonObject11 = jsonArray22.getJSONObject(0);
        JdbcTemplate dbTemplate = dbServer.getJdbcTemplate(jsonObject11.getInteger("dbInfoId"));
        List<DataPermissions> json = dataPermissionsDao.selectList(new QueryWrapper<DataPermissions>() {{
            this.eq("data_userNumber", userInfo.getUserName());
        }});
        ;
        dataPermissionsDao.selectList(new QueryWrapper<DataPermissions>() {{
            this.eq("data_userNumber", userInfo.getUserName());
        }});
        List<Map<String, Object>> mapListu = jdbcTemplate.queryForList("select cTreeStruct from form_list where cTableName='" + cTableName + "'");
        JSONArray jsonArrayu = JSON.parseArray(JSON.toJSONString(mapListu));
        JSONObject jsonObjectu = jsonArrayu.getJSONObject(0);
        String tablee = jsonObjectu.getString("cTreeStruct");
        String tables[] = tablee.replace("[", "").replace("]", "").split(",");
        List list = new ArrayList();
        for (int s = 0; s < tables.length; s++) {
            list.add(tables[s]);
        }

//		String o = tableValue;
        List<Map<String, Object>> mapListss = dbTemplate.queryForList("select *  from  " + tableField.replace("_id", "") + "");
        JSONArray jsonArrayss = JSON.parseArray(JSON.toJSONString(mapListss));

        for (int s = 0; s < jsonArrayss.size(); s++) {
            JSONObject jsonObject12 = jsonArrayss.getJSONObject(s);
            List<Map<String, Object>> mapLists = dbTemplate.queryForList("select  * from  " + tableField.replace("_id", "") + " where " + tableField.replace("_id", "") + "_id =" + tableValue + "");
            JSONArray jsonArrays = JSON.parseArray(JSON.toJSONString(mapLists));
            JSONObject jsonObjects = jsonArrays.getJSONObject(0);
            //		System.out.println(jsonObjects.getString("" + tableField + ""));
            //			System.out.println(jsonObject12.getString("parent_level"));
            if (jsonObjects.getString("" + tableField + "").equals(jsonObject12.getString("parent_level"))) {
                sBuffer.append(" or  " + tableField + "=" + jsonObject12.getString("" + tableField + "") + " ");
//					   o=jsonObject12.getString(""+tableField+"");
            }
        }
        if (json.size() > 0) {
            sBuffer.append(" and ");
//			System.out.println("sssss"+tableField.replace("_id",""));
            //		System.out.println("sd"+cTableName);
            DataPermissions jsonObject1 = null;
            List<Map<String, Object>> mapList5 = null;
            for (int i = 0; i < json.size(); i++) {
                jsonObject1 = json.get(i);

                mapList5 = dbTemplate.queryForList("SELECT * FROM syscolumns WHERE id=OBJECT_ID('" + cTableName + "') AND name='" + jsonObject1.getTableName() + "_id'");
                List<Map<String, Object>> mapList1 = dbTemplate.queryForList("select  * from  " + jsonObject1.getTableName() + " where " + jsonObject1.getTableName() + "_id =" + jsonObject1.getId() + "");
                JSONArray jsonArray1 = JSON.parseArray(JSON.toJSONString(mapList1));
                JSONObject jsonObject2 = jsonArray1.getJSONObject(0);

                List<Map<String, Object>> mapList2 = dbTemplate.queryForList("select *  from  " + jsonObject1.getTableName() + "");
                JSONArray jsonArray2 = JSON.parseArray(JSON.toJSONString(mapList2));
                String a = jsonObject1.getId();
                String b = jsonObject1.getTableName() + "_id";
                String g = jsonObject1.getId();
                if (tableField.replace("_id", "").equals(jsonObject1.getTableName())) {
                    if (i == 0) {
                        sBuffer.delete(sBuffer.toString().lastIndexOf(" and "), sBuffer.length());
                    }
                    for (int s = 0; s < jsonArrayss.size(); s++) {
                        JSONObject jsonObject12 = jsonArrayss.getJSONObject(s);
                        List<Map<String, Object>> mapLists = dbTemplate.queryForList("select  * from  " + tableField.replace("_id", "") + " where " + tableField.replace("_id", "") + "_id =" + tableValue + "");
                        JSONArray jsonArrays = JSON.parseArray(JSON.toJSONString(mapLists));
                        JSONObject jsonObjects = jsonArrays.getJSONObject(0);
                        //					System.out.println(jsonObjects.getString("" + tableField + ""));
                        //					System.out.println(jsonObject12.getString("parent_level"));
                        if (jsonObjects.getString("" + tableField + "").equals(jsonObject12.getString("parent_level"))) {

                            sBuffer.append(" or " + tableField + "=" + jsonObject12.getString("" + tableField + "") + " ");

//					   o=jsonObject12.getString(""+tableField+"");
                        }
                    }
                } else if (mapList5.size() > 0 && list.contains(jsonObject1.getTableName())) {

                    sBuffer.append(" " + jsonObject1.getTableName() + "_id=" + jsonObject1.getId() + "");
                    sBuffer.append(" or ");
                    for (int c = 0; c < jsonArray2.size(); c++) {
                        JSONObject jsonObject3 = jsonArray2.getJSONObject(c);


                        List<Map<String, Object>> mapList0 = dbTemplate.queryForList("select  * from  " + jsonObject1.getTableName() + " where " + jsonObject1.getTableName() + "_id" + " =" + jsonObject1.getId() + "");
                        JSONArray jsonArray0 = JSON.parseArray(JSON.toJSONString(mapList0));
                        JSONObject jsonObject0 = jsonArray0.getJSONObject(0);
                        if (jsonObject0.getString("" + jsonObject1.getTableName() + "_id").equals(jsonObject3.getString("parent_level"))) {
                            sBuffer.append("   " + jsonObject1.getTableName() + "_id =" + jsonObject3.getString("" + jsonObject1.getTableName() + "_id") + " ");
                            sBuffer.append(" or ");
//									g = jsonObject3.getString("" + jsonObject1.getTableName() + "_id");
                        } else if (jsonObject0.getString("parent_level").equals(jsonObject3.getString("" + jsonObject1.getTableName() + "_id"))) {
                            sBuffer.append("   " + jsonObject1.getTableName() + "_id =" + jsonObject3.getString("" + jsonObject1.getTableName() + "_id") + " ");
                            sBuffer.append(" or ");
                        }
                    }
                    for (int c = 0; c < jsonArray2.size(); c++) {
                        JSONObject jsonObject3 = jsonArray2.getJSONObject(c);
                        List<Map<String, Object>> mapList0 = dbTemplate.queryForList("select  * from  " + jsonObject1.getTableName() + " where " + jsonObject1.getTableName() + "_id" + " =" + g + "");
                        JSONArray jsonArray0 = JSON.parseArray(JSON.toJSONString(mapList0));
                        JSONObject jsonObject0 = jsonArray0.getJSONObject(0);
                        if (jsonObject0.getString("" + jsonObject1.getTableName() + "_id").equals(jsonObject3.getString("parent_level"))) {
                            sBuffer.append("   " + jsonObject1.getTableName() + "_id =" + jsonObject3.getString("" + jsonObject1.getTableName() + "_id") + " ");
                            sBuffer.append(" or ");
                            g = jsonObject3.getString("" + jsonObject1.getTableName() + "_id");
                        } else if (jsonObject0.getString("parent_level").equals(jsonObject3.getString("" + jsonObject1.getTableName() + "_id"))) {
                            sBuffer.append("   " + jsonObject1.getTableName() + "_id =" + jsonObject3.getString("" + jsonObject1.getTableName() + "_id") + " ");
                            sBuffer.append(" or ");
                        }
                    }

                }

            }
            if (tableField.replace("_id", "").equals(jsonObject1.getTableName())) {

            } else if (mapList5.size() > 0 && list.contains(jsonObject1.getTableName())) {
                sBuffer.delete(sBuffer.toString().lastIndexOf("or"), sBuffer.length());
            } else {
                sBuffer.delete(sBuffer.toString().lastIndexOf("and"), sBuffer.length());
            }


        }

        //	System.out.println("ssss"+sBuffer);
        return sBuffer.toString();

    }

    //根据流程节点Id获取任务id
    public String findByprocessInstanceId(String tableName, Map<String, String> maps) {
        // TODO Auto-generated method stub
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select * from " + tableName + " where ");
        for (Entry<String, String> map : maps.entrySet()) {
            stringBuffer.append(map.getKey() + " = '" + map.getValue() + "'");
        }


        //	System.out.println(stringBuffer.toString());
        return stringBuffer.toString();
    }


    public String insertField(String tableName, String field, String fieldType) {
        // TODO Auto-generated method stub
        String sql = "alter table " + tableName + " add " + field + " " + fieldType + " not null default 0";


        return sql;
    }

    public String treeSql(String tableName, String[] treeTableName) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select * from " + tableName + " left join");
//		for(int i = 0; ) {
//
//		}
//		select * from apartment2 left join
//		company2 on apartment2.company2_id
//		= company2.id left join group2 on
//		apartment2.group2_id = group2.id
//
        return null;
    }

    @SuppressWarnings("all")
    public String createTree(String tableName,
                             List<String> fieldList,
                             List<String> tableList,
                             String parent_level,
                             int tree,
                             String treeName,
                             int childrenNumber) throws Exception {
        // TODO Auto-generated method stub
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select t." + tableName + "_id id, ");
        for (int i = 0; i < fieldList.size(); i++) {
            String[] fieldListName = fieldList.get(i).split("_");
            if (fieldListName[fieldListName.length - 1] == "name" || "name".equals(fieldListName[fieldListName.length - 1])) {
                stringBuffer.append("t." + fieldList.get(i) + " name,");
            } else if (fieldListName[fieldListName.length - 1] == "code" || "code".equals(fieldListName[fieldListName.length - 1])) {
                stringBuffer.append("t." + fieldList.get(i) + " code,");
            } else {
                stringBuffer.append("t." + fieldList.get(i) + " " + fieldList.get(i) + ",");
            }
        }
        //stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
        if (childrenNumber == 1) {
            stringBuffer.append(" (SELECT count(1) FROM " + tableList.get(0) + " t1 WHERE t." + tableName + "_id = t1.parent_level ) childrenNumber1,");
            if (tableList.size() > 0) {
                for (int j = 1; j < tableList.size(); j++) {
                    stringBuffer.append(" (SELECT count(1) FROM " + tableList.get(j) + " t"
                            + (j + 1) + " WHERE t." + tableName + "_id = t" + (j + 1) + "." + tableList.get(0) + "_id ) childrenNumber" + (j + 1) + ",");
                }
            }
        } else {
            for (int j = 1; j < tableList.size(); j++) {
                stringBuffer.append(" (SELECT count(1) FROM " + tableList.get(j) + " t"
                        + (j + 1) + " WHERE t." + tableName + "_id = t" + (j + 1) + "." + tableList.get(j) + "_id ) childrenNumber" + (j + 1) + ",");
            }

        }
        stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
        stringBuffer.append(" from " + tableName + " t ");
        if (tree == 1) {
            stringBuffer.append("where t.parent_level='-1'" + "and  t." + treeName + "_id='" + parent_level + "';");
        } else {
            stringBuffer.append(" where t.parent_level='" + parent_level + "'");
        }
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletRequest request = attributes.getRequest();
        HttpSession session = request.getSession();
        UserInfo userInfo = (UserInfo) session.getAttribute(SystemEnum.SESSION_USER.getValue());
        JdbcTemplate jdbcTemplate = msFactory.getJdbcTemplate();
        //	System.out.println("vue"+tableName);
        List<Map<String, Object>> mapList3 = jdbcTemplate.queryForList("select * from table_class where tableClassName='" + tableName + "'");
        JSONArray jsonArray = JSON.parseArray(JSON.toJSONString(mapList3));
        JSONObject jsonObject = jsonArray.getJSONObject(0);
        JdbcTemplate dbTemplate = dbServer.getJdbcTemplate(jsonObject.getInteger("dbInfoId"));
        List<DataPermissions> json = dataPermissionsDao.selectList(new QueryWrapper<DataPermissions>() {{
            this.eq("data_userNumber", userInfo.getUserName());
        }});
        ;
        if (json.size() > 0) {
            stringBuffer.append(" and ");
            List<Map<String, Object>> mapList5 = null;
            DataPermissions dataPermissions = null;
            for (int i = 0; i < json.size(); i++) {
                dataPermissions = json.get(i);

                List<Map<String, Object>> mapList1 = dbTemplate.queryForList("select  * from  " + dataPermissions.getTableName() + " where " + dataPermissions.getTableName() + "_id =" + dataPermissions.getId() + "");
                JSONArray jsonArray1 = JSON.parseArray(JSON.toJSONString(mapList1));
                JSONObject jsonObject2 = jsonArray1.getJSONObject(0);
                List<Map<String, Object>> mapList2 = dbTemplate.queryForList("select *  from  " + dataPermissions.getTableName() + "");
                JSONArray jsonArray2 = JSON.parseArray(JSON.toJSONString(mapList2));
                String a = dataPermissions.getId();
                String b = dataPermissions.getTableName() + "_id";
                String l = dataPermissions.getTableName() + "_id";
                String g = dataPermissions.getId();


                mapList5 = dbTemplate.queryForList("SELECT * FROM syscolumns WHERE id=OBJECT_ID('" + tableName + "') AND name='" + dataPermissions.getTableName() + "_id'");
                if (mapList5.size() > 0) {
                    stringBuffer.append("" + dataPermissions.getTableName() + "_id = " + jsonObject2.getString("" + dataPermissions.getTableName() + "_id"));
                    stringBuffer.append(" or ");
                    for (int c = 0; c < jsonArray2.size(); c++) {
                        JSONObject jsonObject3 = jsonArray2.getJSONObject(c);
                        List<Map<String, Object>> mapList0 = dbTemplate.queryForList("select  * from  " + dataPermissions.getTableName() + " where " + l + "" + " =" + a + "");
                        JSONArray jsonArray0 = JSON.parseArray(JSON.toJSONString(mapList0));
                        JSONObject jsonObject0 = jsonArray0.getJSONObject(0);
                        if (jsonObject0.getString("parent_level").equals(jsonObject3.getString("" + dataPermissions.getTableName() + "_id"))) {

                            stringBuffer.append(" " + dataPermissions.getTableName() + "_id =" + jsonObject3.getString("" + dataPermissions.getTableName() + "_id") + "");
                            stringBuffer.append(" or ");
                            a = jsonObject0.getString("parent_level");
                        } else if (jsonObject0.getString("" + dataPermissions.getTableName() + "_id").equals(jsonObject3.getString("parent_level"))) {
                            stringBuffer.append("" + dataPermissions.getTableName() + "_id = " + jsonObject3.getString("" + dataPermissions.getTableName() + "_id") + "");
                            stringBuffer.append(" or ");
                            l = "parent_level";
                            a = jsonObject0.getString("" + dataPermissions.getTableName() + "_id");
                        }
                    }

                } else if (tableList.contains(dataPermissions.getTableName())) {
                    List<Map<String, Object>> mapList0 = dbTemplate.queryForList("select  * from  " + dataPermissions.getTableName() + " where " + dataPermissions.getTableName() + "_id" + " =" + dataPermissions.getId() + "");
                    JSONArray jsonArray3 = JSON.parseArray(JSON.toJSONString(mapList0));
                    JSONObject jsonObject3 = jsonArray3.getJSONObject(0);
                    stringBuffer.append("  " + tableName + "_id=" + jsonObject3.getString("" + dataPermissions.getTableName() + "_id") + "");
                    stringBuffer.append(" or ");

                }


            }
            if (mapList5.size() > 0 || tableList.contains(dataPermissions.getTableName())) {
                stringBuffer.delete(stringBuffer.toString().lastIndexOf("or"), stringBuffer.length());
            } else {
                stringBuffer.delete(stringBuffer.toString().lastIndexOf("and"), stringBuffer.length());
            }
        }

//		System.out.println("ssssssss"+stringBuffer);
        return stringBuffer.toString();
    }

    @SuppressWarnings("all")
    public String createCompanyJsonSql(String tableName, List<String> fieldList, List<String> tableList,
                                       String parentId) throws Exception {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select t." + tableName + "_id id, ");
        for (int i = 0; i < fieldList.size(); i++) {
            String[] fieldListName = fieldList.get(i).split("_");
            if (fieldListName[fieldListName.length - 1] == "name" || "name".equals(fieldListName[fieldListName.length - 1])) {
                stringBuffer.append("t." + fieldList.get(i) + " name,");
            } else if (fieldListName[fieldListName.length - 1] == "code" || "code".equals(fieldListName[fieldListName.length - 1])) {
                stringBuffer.append("t." + fieldList.get(i) + " code,");
            } else {
                stringBuffer.append("t." + fieldList.get(i) + " " + fieldList.get(i) + ",");

            }

        }
        stringBuffer.append("(select count(1) from " + tableList.get(1) + " t1 where t." + tableName + "_id=t1.parent_level) childrenNumber2,");
        if (tableList.size() > 2) {
            stringBuffer.append("(select count(1) from " + tableList.get(2) + " t3 where t." + tableName + "_id=t3." + tableList.get(1) + "_id) childrenNumber3,");

        }
        stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
        stringBuffer.append(" FROM " + tableName + "  t where t.parent_level='-1' and t." + tableList.get(0) + "_id='" + parentId + "'");
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletRequest request = attributes.getRequest();
        HttpSession session = request.getSession();
        UserInfo userInfo = (UserInfo) session.getAttribute(SystemEnum.SESSION_USER.getValue());
        JdbcTemplate jdbcTemplate = msFactory.getJdbcTemplate();
        List<Map<String, Object>> mapList3 = jdbcTemplate.queryForList("select * from table_class where tableClassName='" + tableName + "'");
        JSONArray jsonArray = JSON.parseArray(JSON.toJSONString(mapList3));
        JSONObject jsonObject = jsonArray.getJSONObject(0);
        JdbcTemplate dbTemplate = dbServer.getJdbcTemplate(jsonObject.getInteger("dbInfoId"));
        List<DataPermissions> json = dataPermissionsDao.selectList(new QueryWrapper<DataPermissions>() {{
            this.eq("data_userNumber", userInfo.getUserName());
        }});
        ;
        if (json.size() > 0) {
            stringBuffer.append(" and ");
            List<Map<String, Object>> mapList5 = null;
            DataPermissions dataPermissions = null;
            for (int i = 0; i < json.size(); i++) {
                dataPermissions = json.get(i);
                mapList5 = dbTemplate.queryForList("SELECT * FROM syscolumns WHERE id=OBJECT_ID('" + dataPermissions.getTableName() + "') AND name='" + tableName + "_id'");
                List<Map<String, Object>> mapList1 = dbTemplate.queryForList("select  * from  " + dataPermissions.getTableName() + " where " + dataPermissions.getTableName() + "_id =" + dataPermissions.getId() + "");
                JSONArray jsonArray1 = JSON.parseArray(JSON.toJSONString(mapList1));
                JSONObject jsonObject2 = jsonArray1.getJSONObject(0);
                List<Map<String, Object>> mapList2 = dbTemplate.queryForList("select *  from  " + dataPermissions.getTableName() + "");
                JSONArray jsonArray2 = JSON.parseArray(JSON.toJSONString(mapList2));
                String a = dataPermissions.getId();
                String b = dataPermissions.getTableName() + "_id";
                String g = dataPermissions.getId();
                String l = dataPermissions.getTableName() + "_id";

                mapList5 = dbTemplate.queryForList("SELECT * FROM syscolumns WHERE id=OBJECT_ID('" + tableName + "') AND name='" + dataPermissions.getTableName() + "_id'");
                if (mapList5.size() > 0) {
                    stringBuffer.append("" + dataPermissions.getTableName() + "_id = " + jsonObject2.getString("" + dataPermissions.getTableName() + "_id"));
                    stringBuffer.append(" or ");
                    for (int c = 0; c < jsonArray2.size(); c++) {
                        JSONObject jsonObject3 = jsonArray2.getJSONObject(c);
                        List<Map<String, Object>> mapList0 = dbTemplate.queryForList("select  * from  " + dataPermissions.getTableName() + " where " + l + "" + " =" + a + "");
                        JSONArray jsonArray0 = JSON.parseArray(JSON.toJSONString(mapList0));
                        JSONObject jsonObject0 = jsonArray0.getJSONObject(0);
                        if (jsonObject0.getString("parent_level").equals(jsonObject3.getString("" + dataPermissions.getTableName() + "_id"))) {

                            stringBuffer.append(" " + dataPermissions.getTableName() + "_id =" + jsonObject3.getString("" + dataPermissions.getTableName() + "_id") + "");
                            stringBuffer.append(" or ");
                            a = jsonObject0.getString("parent_level");
                        } else if (jsonObject0.getString("" + dataPermissions.getTableName() + "_id").equals(jsonObject3.getString("parent_level"))) {
                            stringBuffer.append("" + dataPermissions.getTableName() + "_id = " + jsonObject3.getString("" + dataPermissions.getTableName() + "_id") + "");
                            stringBuffer.append(" or ");
                            l = "parent_level";
                            a = jsonObject0.getString("" + dataPermissions.getTableName() + "_id");
                        }
                    }


                } else if (tableList.contains(dataPermissions.getTableName())) {
                    List<Map<String, Object>> mapList0 = dbTemplate.queryForList("select  * from  " + dataPermissions.getTableName() + " where " + dataPermissions.getTableName() + "_id" + " =" + dataPermissions.getId() + "");
                    JSONArray jsonArray3 = JSON.parseArray(JSON.toJSONString(mapList0));
                    JSONObject jsonObject3 = jsonArray3.getJSONObject(0);
                    stringBuffer.append("  " + tableName + "_id=" + jsonObject3.getString("" + dataPermissions.getTableName() + "_id") + "");
                    stringBuffer.append(" or ");

                }

            }
            if (mapList5.size() > 0 || tableList.contains(dataPermissions.getTableName())) {
                stringBuffer.delete(stringBuffer.toString().lastIndexOf("or"), stringBuffer.length());
            } else {
                stringBuffer.delete(stringBuffer.toString().lastIndexOf("and"), stringBuffer.length());
            }
        }

        //	System.out.println("vsolud"+stringBuffer);
        return stringBuffer.toString();

    }


    @SuppressWarnings("all")
    public String apaTree(String tableName,
                          List<String> fieldList,
                          List<String> tableList,
                          String parentId) {

//		SELECT \n" +
//        "t.id \"id\",\n" +
//        "t.apartment2_name \"name\",\n" +
//        "t.parent_level \"parentId\",\n" +
//        "t.group2_id \"groupId\",\n" +
//        "t.company2_id \"companyId\",\n" +
//        "(select count(1) from apartment2 t2 where t.id=t2.parent_level) \"childrenNumber\"\n" +
//        " FROM bpm_test.apartment2 t where t.parent_level='-1' and  t.group2_id='"+parentId+"';"
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select t." + tableName + "_id id, ");
        for (int i = 0; i < fieldList.size(); i++) {
            String[] fieldListName = fieldList.get(i).split("_");
            if (fieldListName[fieldListName.length - 1] == "name" || "name".equals(fieldListName[fieldListName.length - 1])) {
                stringBuffer.append("t." + fieldList.get(i) + " name,");
            } else if (fieldListName[fieldListName.length - 1] == "code" || "code".equals(fieldListName[fieldListName.length - 1])) {
                stringBuffer.append("t." + fieldList.get(i) + " code,");
            } else {
                stringBuffer.append("t." + fieldList.get(i) + " " + fieldList.get(i) + ",");
            }
        }
        //stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
        stringBuffer.append(" (SELECT count(1) FROM " + tableList.get(1)
                + " t1 WHERE t." + tableList.get(0) + "_id = t1." + tableList.get(1) + "_id ) childrenNumber1,");
        stringBuffer.append(" (SELECT count(1) FROM " + tableList.get(1)
                + " t1 WHERE t." + tableList.get(1) + "_id = t1." + tableList.get(1) + "_id ) childrenNumber2,");
        stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
        stringBuffer.append(" from " + tableName + " t " + "where t.parent_level='-1' and t." + tableName + "_id='" + parentId + "';");
        return stringBuffer.toString();
    }


    @SuppressWarnings("all")
    public String apaTree1(String tableName, List<String> fieldList, List<String> tableList, String parentId) throws Exception {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select t." + tableName + "_id id, ");
        for (int i = 0; i < fieldList.size(); i++) {
            String[] fieldListName = fieldList.get(i).split("_");
            if (fieldListName[fieldListName.length - 1] == "name" || "name".equals(fieldListName[fieldListName.length - 1])) {
                stringBuffer.append("t." + fieldList.get(i) + " name,");
            } else if (fieldListName[fieldListName.length - 1] == "code" || "code".equals(fieldListName[fieldListName.length - 1])) {
                stringBuffer.append("t." + fieldList.get(i) + " code,");
            } else {
                stringBuffer.append("t." + fieldList.get(i) + " " + fieldList.get(i) + ",");
            }
        }
        //stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
        stringBuffer.append(" (SELECT count(1) FROM " + tableList.get(1)
                + " t1 WHERE t." + tableList.get(1) + "_id = t1." + tableList.get(1) + "_id) childrenNumber1,");
        stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
        stringBuffer.append(" from " + tableName + " t " + "where t.parent_level='-1' and t." + tableList.get(1) + "_id='" + parentId + "'");

        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletRequest request = attributes.getRequest();
        HttpSession session = request.getSession();
        UserInfo userInfo = (UserInfo) session.getAttribute(SystemEnum.SESSION_USER.getValue());
        JdbcTemplate jdbcTemplate = msFactory.getJdbcTemplate();
        List<Map<String, Object>> mapList3 = jdbcTemplate.queryForList("select * from table_class where tableClassName='" + tableName + "'");
        JSONArray jsonArray = JSON.parseArray(JSON.toJSONString(mapList3));
        JSONObject jsonObject = jsonArray.getJSONObject(0);
        JdbcTemplate dbTemplate = dbServer.getJdbcTemplate(jsonObject.getInteger("dbInfoId"));
        List<DataPermissions> json = dataPermissionsDao.selectList(new QueryWrapper<DataPermissions>() {{
            this.eq("data_userNumber", userInfo.getUserName());
        }});
        ;
        if (json.size() > 0) {
            stringBuffer.append(" and ");
            List<Map<String, Object>> mapList5 = null;
            for (int i = 0; i < json.size(); i++) {
                DataPermissions dataPermissions = json.get(i);
                mapList5 = dbTemplate.queryForList("SELECT * FROM syscolumns WHERE id=OBJECT_ID('" + dataPermissions.getTableName() + "') AND name='" + tableName + "_id'");
                List<Map<String, Object>> mapList1 = dbTemplate.queryForList("select  * from  " + dataPermissions.getTableName() + " where " + dataPermissions.getTableName() + "_id =" + dataPermissions.getId() + "");
                JSONArray jsonArray1 = JSON.parseArray(JSON.toJSONString(mapList1));
                JSONObject jsonObject2 = jsonArray1.getJSONObject(0);
                List<Map<String, Object>> mapList2 = dbTemplate.queryForList("select *  from  " + dataPermissions.getTableName() + "");
                JSONArray jsonArray2 = JSON.parseArray(JSON.toJSONString(mapList2));
                String a = dataPermissions.getId();
                if (mapList5.size() > 0) {
                    stringBuffer.append("  " + dataPermissions.getTableName() + "_id = " + jsonObject2.getString("" + dataPermissions.getTableName() + "_id") + "");
                    stringBuffer.append(" or ");
                    for (int c = 0; c < jsonArray2.size(); c++) {
                        List<Map<String, Object>> mapList0 = dbTemplate.queryForList("select  * from  " + dataPermissions.getTableName() + " where " + dataPermissions.getTableName() + "_id =" + a + "");
                        JSONArray jsonArray0 = JSON.parseArray(JSON.toJSONString(mapList0));
                        JSONObject jsonObject0 = jsonArray0.getJSONObject(0);
                        if (jsonObject0.getInteger("parent_level") != -1) {

                            stringBuffer.append("  " + tableName + "_id =" + jsonObject0.getString("parent_level") + "");
                            stringBuffer.append(" or ");
                            a = jsonObject0.getString("parent_level");
                        }

                    }
                }

            }
            if (mapList5.size() > 0) {
                stringBuffer.delete(stringBuffer.toString().lastIndexOf("or"), stringBuffer.length());
            } else {
                stringBuffer.delete(stringBuffer.toString().lastIndexOf("and"), stringBuffer.length());
            }
        }

        //	System.out.println("sbsbsbsb"+stringBuffer);


        return stringBuffer.toString();
    }

    @SuppressWarnings("all")
    public String apaTree2(String tableName, List<String> fieldList, List<String> tableList, String parentId) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select t." + tableName + "_id id, ");
        for (int i = 0; i < fieldList.size(); i++) {
            String[] fieldListName = fieldList.get(i).split("_");
            if (fieldListName[fieldListName.length - 1] == "name" || "name".equals(fieldListName[fieldListName.length - 1])) {
                stringBuffer.append("t." + fieldList.get(i) + " name,");
            } else if (fieldListName[fieldListName.length - 1] == "code" || "code".equals(fieldListName[fieldListName.length - 1])) {
                stringBuffer.append("t." + fieldList.get(i) + " code,");
            } else {
                stringBuffer.append("t." + fieldList.get(i) + " " + fieldList.get(i) + ",");
            }
        }

        stringBuffer.append(" (SELECT count(1) FROM " + tableName
                + " t1 WHERE t." + tableName + "_id = t1.parent_level) childrenNumber1,");
        stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
        stringBuffer.append(" from " + tableName + " t " + "where t.parent_level='" + parentId + "'");


//		System.out.println("ssssssss"+stringBuffer);
        return stringBuffer.toString();

    }


    @SuppressWarnings("all")
    public String comTree1(String tableName, List<String> fieldList, List<String> tableList, String parentId) {
        StringBuffer stringBuffer = new StringBuffer();
//		 Object companyObject = jdbCutils.excuteQuery(" SELECT \n" +
//	                "t.id \"id\",\n" +
//	                "t.company2_name \"name\",\n" +
//	                "t.parent_level \"parentId\",\n" +
//	                "t.group2_id \"groupId\",\n" +
//	                "(select count(1) from company2 t1 where t.id=t1.parent_level) \"childrenNumber2\",\n" +
//	                "(select count(1) from apartment2 t3 where t.id=t3.company2_id) \"childrenNumber3\"\n" +
//	                " FROM bpm_test.company2  t where t.parent_level='"+parentId+"';", null);


        stringBuffer.append("select t." + tableName + "_id id, ");
        for (int i = 0; i < fieldList.size(); i++) {
            stringBuffer.append("t." + fieldList.get(i) + " " + fieldList.get(i) + ",");

        }
        //stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
        stringBuffer.append(" (SELECT count(1) FROM " + tableName
                + " t1 WHERE t." + tableName + "_id = t1.parent_level) childrenNumber1,");
        //stringBuffer.append(" (SELECT count(1) FROM "+ tableList.get(0)
        //	+" t1 WHERE t.id = t1."+tableName+"_id) childrenNumber2,");
        stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
        stringBuffer.append(" from " + tableName + " t " + "where t.parent_level='" + parentId + "';");
        return stringBuffer.toString();
    }

    @Override
    //参照默认查询
    public JSONArray casefind(int formListId) throws Exception {


        FormList formList = formListDao.selectById(formListId);
        ;
        formListDao.selectById(formListId);
        String cTableName = formList.getCTableName();

        //获取数据库链接对象
        List<TableClass> tableClasss = tableClassDao.selectList(new QueryWrapper<TableClass>() {{
            this.eq("tableClassName", (cTableName));
        }});
        TableClass tableClass = tableClasss.get(0);
        JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(tableClass.getDbInfoId());

        List<CreateTable> createTables = createTableDao.selectList(new QueryWrapper<CreateTable>() {{
            this.eq("tableName", cTableName);
        }});
        ;

        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select ");

        for (int b = 0; b < createTables.size(); b++) {
            CreateTable createTable = createTables.get(b);
            FormLtem formltems = formItemsDao.selectOne(new QueryWrapper<FormLtem>() {{
                this.eq("cTableField", createTable.getField());
                this.eq("cTableName", cTableName);
            }});
            if ("datetime".equals(createTable.getType())) {
                stringBuffer.append("( select convert(varchar(100)," + createTable.getField() + ",120)) as " + createTable.getField() + ",");
            } else if (formltems != null) {
                if (!"{}".equals(formltems.getRef()) && createTable.getField() != "parent_level") {
                    JSONObject refJSON = JSON.parseObject(formltems.getRef());
                    JSONObject ref = JSON.parseObject(refJSON.getString("ref"));
                    stringBuffer.append("(select " + ref.getString("cTableField") + " from " + ref.getString("cTableName") + " where "
                            + ref.getString("cTableName") + "_id=" + cTableName + "." + formltems.getCTableField() + " ) as " + createTable.getField() + ",");
                } else {
                    stringBuffer.append(cTableName + "." + createTable.getField() + " as " + createTable.getField() + ",");
                }
            } else if (createTable.getField() == "parent_level") {
                stringBuffer.append("(select  " + cTableName + "_name from " + cTableName + " where " + cTableName + "_id =" + cTableName + ")");
            }
        }
        stringBuffer.delete(stringBuffer.toString().lastIndexOf(","), stringBuffer.length());
        stringBuffer.append(" from " + cTableName);
        String query = formList.getFindQuery();
        if (query != null && !"".equals(query)) {
            stringBuffer.append(" where " + query);
        }

        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(stringBuffer.toString());
        JSONArray jsonArray = JSON.parseArray(JSON.toJSONString(mapList));
        return jsonArray;
    }

    /**
     * 全局查询
     *
     * @param formListId 表单id
     * @param findQuery  单个查询条件
     * @return
     */
    @Override
    public List<Map<String, Object>> findByLike(int formListId, String findQuery) throws Exception {


        //查询表单
        FormList formList = formListDao.selectById(formListId);
        ;
        //根据表单获取主表名
        String mTable = formList.getCTableName();
        //根据主表名获取详细信息
        List<TableClass> tableClasses = tableClassDao.selectList(new QueryWrapper<TableClass>() {{
            this.eq("tableClassName", (mTable));
        }});
        TableClass tableClass = tableClasses.get(0);
        //获取主表明细
        List<CreateTable> createTables = createTableDao.selectList(new QueryWrapper<CreateTable>() {{
            this.eq("tableName", mTable);
        }});
        ;

        //根据主表信息获取数据库链接对象
        JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(tableClass.getDbInfoId());
        //sql语句拼接
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select * from " + mTable + " where ");
        for (int i = 0; i < createTables.size(); i++) {
            CreateTable createTable = createTables.get(i);
            stringBuffer.append(createTable.getField() + " like '%" + findQuery + "%' or ");
        }
        stringBuffer.delete(stringBuffer.toString().lastIndexOf("or"), stringBuffer.length());
        //获取查询返回的list<Map>
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(stringBuffer.toString());
        return mapList;
    }

    /**
     * 表单过滤查询
     *
     * @param str
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String, Object>> findFliter(String str) throws Exception {
        JSONObject jsonstr = null;

        JSONObject jsonObject = JSONArray.parseObject(str);
        //	System.out.println("jsonObject>>>>>>><<<<<<<<" + jsonObject);
        String colVal = (String) jsonObject.get("colVal");
        String cTableName = colVal.split("_")[0];

        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletRequest request = attributes.getRequest();
        HttpSession session = request.getSession();
        UserInfo userInfo = (UserInfo) session.getAttribute(SystemEnum.SESSION_USER.getValue());
        List<UserinfoJurisdiction> mapLists = userinfo_jurisdictionDao.selectList(new QueryWrapper<UserinfoJurisdiction>() {{
            this.eq("userNumber", userInfo.getUserName());
        }});
        StringBuffer stringBuffer = new StringBuffer();
        Object keyVal = jsonObject.get("keyVal");
        stringBuffer.append("select * from " + cTableName + " where " + colVal + " like '%" + keyVal + "%' and " + cTableName + "_single_person='" + userInfo.getUserName() + "'");
        for (UserinfoJurisdiction mapList : mapLists) {
            stringBuffer.append(" or " + cTableName + "_single_person='" + mapList.getUserjurisdiction() + "'");
        }

        List<TableClass> byTableName = tableClassDao.selectList(new QueryWrapper<TableClass>() {{
            this.eq("tableClassName", (cTableName));
        }});
        TableClass tableClass = byTableName.get(0);

        JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(tableClass.getDbInfoId());
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(stringBuffer.toString());

        //	System.out.println("mapList=====================" + mapList);

        return mapList;
    }

    /**
     * 查询差旅住宿标准金额
     *
     * @param beginTime         开始日期
     * @param endTime           结束日期
     * @param province          省市
     * @param area              地区
     * @param selectByTableName 标准金额查询表
     * @return
     */
    @Override
    public String selectRstandardAmount(String beginTime, String endTime, String province, String area, String selectByTableName) throws Exception {

        String amount = "";

        List<TableClass> byTableName = tableClassDao.selectList(new QueryWrapper<TableClass>() {{
            this.eq("tableClassName", (selectByTableName));
        }});
        TableClass tableClass = byTableName.get(0);

        JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(tableClass.getDbInfoId());
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select * from " + selectByTableName + " where ");

        if (province != null && province != "" && area != null && area != "") {
            //通过地区的名字查询地区表的id
            String findSqlByCityName = "select City_id,Provinces_id from City where City_name = '" + area + "'";

            List<Map<String, Object>> CityMapList = jdbcTemplate.queryForList(findSqlByCityName);
            Map<String, Object> cityMap = CityMapList.get(0);
            // 获取地区的id
            int City_id = (int) cityMap.get("City_id");

            // 获取地区所属的省市id
            String provinces_id = (String) cityMap.get("Provinces_id");

            // 通过省市id查询省市表的名称判断与参数名称是否一致
            String findSqlByProvinceId = "select Provinces_name from Provinces where Provinces_id = '" + provinces_id + "'";
            List<Map<String, Object>> provincesMapList = jdbcTemplate.queryForList(findSqlByProvinceId);
            Map<String, Object> provincesMap = provincesMapList.get(0);
            String provinces_name = (String) provincesMap.get("Provinces_name");
            if (province.equals(provinces_name)) { // 名称一致
                stringBuffer.append("rstandard_xgry_0004 = '" + provinces_id + "' and rstandard_xgry_0005 = '" + City_id + "'");

                List<Map<String, Object>> mapList1 = jdbcTemplate.queryForList(stringBuffer.toString());
                if (mapList1.size() > 0 && mapList1.size() == 1) {
                    //	通过申请人获取人员档案中的等级报销标准
                    ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
                    HttpServletRequest request = attributes.getRequest();
                    HttpSession session = request.getSession();
                    UserInfo userInfo = (UserInfo) session.getAttribute(SystemEnum.SESSION_USER.getValue());
                    String findSqlByUserName = "select PersonnelFiles_xgry_0001 from PersonnelFiles where PersonnelFiles_OperatorCode = '" + userInfo.getUserName() + "'";
                    List<Map<String, Object>> personnelFilesMapList = jdbcTemplate.queryForList(findSqlByUserName);
                    Map<String, Object> map = personnelFilesMapList.get(0);
                    String personnelFiles_xgry_0001 = (String) map.get("PersonnelFiles_xgry_0001");

                    Map<String, Object> map1 = mapList1.get(0);

                    if (personnelFiles_xgry_0001.equals("部级标准")) {
                        BigDecimal rstandard_xgry_0006 = (BigDecimal) map1.get("rstandard_xgry_0006");
                        BigDecimal bigDecimal = rstandard_xgry_0006.setScale(2, BigDecimal.ROUND_DOWN);
                        amount = bigDecimal + "";
                    }
                    if (personnelFiles_xgry_0001.equals("司局级标准")) {
                        BigDecimal rstandard_xgry_0007 = (BigDecimal) map1.get("rstandard_xgry_0007");
                        BigDecimal bigDecimal = rstandard_xgry_0007.setScale(2, BigDecimal.ROUND_DOWN);
                        amount = bigDecimal + "";
                    }
                    if (personnelFiles_xgry_0001.equals("处级标准")) { // 人员为处级及以下等级，选择其他人员标准
                        BigDecimal rstandard_xgry_0009 = (BigDecimal) map1.get("rstandard_xgry_0008");
                        BigDecimal bigDecimal = rstandard_xgry_0009.setScale(2, BigDecimal.ROUND_DOWN);
                        amount = bigDecimal + "";
                    }
                    if (personnelFiles_xgry_0001.equals("其他人员标准")) {
                        BigDecimal rstandard_xgry_0008 = (BigDecimal) map1.get("rstandard_xgry_0008");
                        BigDecimal bigDecimal = rstandard_xgry_0008.setScale(2, BigDecimal.ROUND_DOWN);
                        amount = bigDecimal + "";
                    }
                } else if (beginTime != null && endTime != null) {
                    //stringBuffer.append(" and rstandard_xgry_0001 >= '" + beginTime + "' and rstandard_xgry_0002 <= '" + endTime + "'");
                    stringBuffer.append(" and rstandard_xgry_0001 <= '" + beginTime + "' and rstandard_xgry_0002 >= '" + endTime + "'");
                    //stringBuffer.append(" and rstandard_xgry_0001 between '" + beginTime + "' and '" + endTime + "' and rstandard_xgry_0002 between '" + beginTime + "' and '" + endTime + "'");
                    List<Map<String, Object>> mapList = jdbcTemplate.queryForList(stringBuffer.toString());
                    if (mapList.size() > 0) {
                        //	通过申请人获取人员档案中的等级报销标准

                        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
                        HttpServletRequest request = attributes.getRequest();
                        HttpSession session = request.getSession();
                        UserInfo userInfo = (UserInfo) session.getAttribute(SystemEnum.SESSION_USER.getValue());
                        String findSqlByUserName = "select PersonnelFiles_xgry_0001 from PersonnelFiles where PersonnelFiles_OperatorCode = '" + userInfo.getUserName() + "'";
                        List<Map<String, Object>> personnelFilesMapList = jdbcTemplate.queryForList(findSqlByUserName);
                        Map<String, Object> map = personnelFilesMapList.get(0);
                        String personnelFiles_xgry_0001 = (String) map.get("PersonnelFiles_xgry_0001");

                        Map<String, Object> rstandardMap = mapList.get(0);
                        ;
                        if (personnelFiles_xgry_0001.equals("部级标准")) {
                            BigDecimal rstandard_xgry_0006 = (BigDecimal) rstandardMap.get("rstandard_xgry_0006");
                            BigDecimal bigDecimal = rstandard_xgry_0006.setScale(2, BigDecimal.ROUND_DOWN);
                            amount = bigDecimal + "";
                        }
                        if (personnelFiles_xgry_0001.equals("司局级标准")) {
                            BigDecimal rstandard_xgry_0007 = (BigDecimal) rstandardMap.get("rstandard_xgry_0007");
                            BigDecimal bigDecimal = rstandard_xgry_0007.setScale(2, BigDecimal.ROUND_DOWN);
                            amount = bigDecimal + "";
                        }
                        if (personnelFiles_xgry_0001.equals("处级标准")) {
                            BigDecimal rstandard_xgry_0009 = (BigDecimal) rstandardMap.get("rstandard_xgry_0008");
                            BigDecimal bigDecimal = rstandard_xgry_0009.setScale(2, BigDecimal.ROUND_DOWN);
                            amount = bigDecimal + "";
                        }
                        if (personnelFiles_xgry_0001.equals("其他人员标准")) {
                            BigDecimal rstandard_xgry_0008 = (BigDecimal) rstandardMap.get("rstandard_xgry_0008");
                            BigDecimal bigDecimal = rstandard_xgry_0008.setScale(2, BigDecimal.ROUND_DOWN);
                            amount = bigDecimal + "";
                        }
                    } else if (mapList.size() == 0 && beginTime != null && endTime != null) {
                        String sql = "select * from rstandard where rstandard_xgry_0004 = '" + provinces_id + "' and rstandard_xgry_0005 = '" + City_id + "' and rstandard_xgry_0001 is null and rstandard_xgry_0002 is null";
                        List<Map<String, Object>> mapList2 = jdbcTemplate.queryForList(sql);
                        if (mapList2.size() > 0) {
                            //	通过申请人获取人员档案中的等级报销标准

                            ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
                            HttpServletRequest request = attributes.getRequest();
                            HttpSession session = request.getSession();
                            UserInfo userInfo = (UserInfo) session.getAttribute(SystemEnum.SESSION_USER.getValue());
                            String findSqlByUserName = "select PersonnelFiles_xgry_0001 from PersonnelFiles where PersonnelFiles_OperatorCode = '" + userInfo.getUserName() + "'";
                            List<Map<String, Object>> personnelFilesMapList = jdbcTemplate.queryForList(findSqlByUserName);
                            Map<String, Object> map = personnelFilesMapList.get(0);
                            String personnelFiles_xgry_0001 = (String) map.get("PersonnelFiles_xgry_0001");

                            Map<String, Object> rstandardMap = mapList2.get(0);
                            ;
                            if (personnelFiles_xgry_0001.equals("部级标准")) {
                                BigDecimal rstandard_xgry_0006 = (BigDecimal) rstandardMap.get("rstandard_xgry_0006");
                                BigDecimal bigDecimal = rstandard_xgry_0006.setScale(2, BigDecimal.ROUND_DOWN);
                                amount = bigDecimal + "";
                            }
                            if (personnelFiles_xgry_0001.equals("司局级标准")) {
                                BigDecimal rstandard_xgry_0007 = (BigDecimal) rstandardMap.get("rstandard_xgry_0007");
                                BigDecimal bigDecimal = rstandard_xgry_0007.setScale(2, BigDecimal.ROUND_DOWN);
                                amount = bigDecimal + "";
                            }
                            if (personnelFiles_xgry_0001.equals("处级标准")) {
                                BigDecimal rstandard_xgry_0009 = (BigDecimal) rstandardMap.get("rstandard_xgry_0009");
                                BigDecimal bigDecimal = rstandard_xgry_0009.setScale(2, BigDecimal.ROUND_DOWN);
                                amount = bigDecimal + "";
                            }
                            if (personnelFiles_xgry_0001.equals("其他人员标准")) {
                                BigDecimal rstandard_xgry_0008 = (BigDecimal) rstandardMap.get("rstandard_xgry_0008");
                                BigDecimal bigDecimal = rstandard_xgry_0008.setScale(2, BigDecimal.ROUND_DOWN);
                                amount = bigDecimal + "";
                            }
                        }
                    } else {
                        amount = "请选择正确淡旺季日期范围";
                    }
                } else {
                    amount = "请选择日期";
                }
            } else { // 地区中没有对应的省市
                amount = "省市与地区不匹配";
            }
        }
        return amount;
    }

    /**
     * 查询差旅补贴标准金额
     *
     * @param province          省市
     * @param area              地区
     * @param selectByTableName 标准金额查询表
     * @return
     */
    @Override
    public String selectTravelSubsidyStandardAmount(String province, String area, String selectByTableName) throws Exception {
        String amount = "";

        List<TableClass> byTableName = tableClassDao.selectList(new QueryWrapper<TableClass>() {{
            this.eq("tableClassName", (selectByTableName));
        }});
        TableClass tableClass = byTableName.get(0);

        JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(tableClass.getDbInfoId());
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select * from " + selectByTableName + " where ");

        if (province != null && province != "" && area != null && area != "") {
            //通过地区的名字查询地区表的id
            String findSqlByCityName = "select City_id,Provinces_id from City where City_name = '" + area + "'";

            List<Map<String, Object>> CityMapList = jdbcTemplate.queryForList(findSqlByCityName);
            Map<String, Object> cityMap = CityMapList.get(0);
            // 获取地区的id
            int City_id = (int) cityMap.get("City_id");

            // 获取地区所属的省市id
            String provinces_id = (String) cityMap.get("Provinces_id");

            // 通过省市id查询省市表的名称判断与参数名称是否一致
            String findSqlByProvinceId = "select Provinces_name from Provinces where Provinces_id = '" + provinces_id + "'";
            List<Map<String, Object>> provincesMapList = jdbcTemplate.queryForList(findSqlByProvinceId);
            Map<String, Object> provincesMap = provincesMapList.get(0);
            String provinces_name = (String) provincesMap.get("Provinces_name");
            if (province.equals(provinces_name)) { // 名称一致
                stringBuffer.append("TravelSubsidyStandard_xgry_0001 = '" + provinces_id + "' and TravelSubsidyStandard_xgry_0002 = '" + City_id + "'");
                List<Map<String, Object>> mapList = jdbcTemplate.queryForList(stringBuffer.toString());
                if (mapList.size() > 0) {
                    Map<String, Object> map = mapList.get(0);
                    BigDecimal travelSubsidyStandardAmount = (BigDecimal) map.get("TravelSubsidyStandard_xgry_0006");
                    BigDecimal bigDecimal = travelSubsidyStandardAmount.setScale(2, BigDecimal.ROUND_DOWN);
                    amount = bigDecimal + "";
                } else {
                    amount = "选择的地区没有对应标准金额";
                }
            } else { // 地区中没有对应的省市
                amount = "省市与地区不匹配";
            }
        }
        return amount;
    }

    /**
     * 查询报销管理下的单据所对应的表名称
     *
     * @return
     */
    @Override
    public List<Map<String, Object>> findTableName() throws Exception {

        List<Map<String, Object>> list = new ArrayList<>();

        //	在form_ltem下查询报销管理下的所有单据对应表名称(查询出的数据包含主次表)
        String sql1 = "select cTableName from form_ltem where modularId = 59  or parId = 59 GROUP BY cTableName";
        JdbcTemplate jdbcTemplate = msFactory.getJdbcTemplate();
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql1);

        // 在table_class中查询出所有的主表单据名称
        String sql2 = "SELECT tableChClassName,tableClassName,dbInfoId FROM table_class where mTableId = 0";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql2);

        if (mapList.size() > 0 && maps.size() > 0) { // 找出两个结果集中相同的表名为最终的报销管理下的主表单据
            for (Map<String, Object> map1 : mapList) {
                String cTableName = (String) map1.get("cTableName");
                for (Map<String, Object> map2 : maps) {
                    String tableClassName = (String) map2.get("tableClassName");
                    String tableChClassName = (String) map2.get("tableChClassName");
                    int dbInfoId = (int) map2.get("dbInfoId");
                    if (cTableName.equals(tableClassName)) {
                        Map<String, Object> map = new HashMap<>();
                        map.put("cTableName", cTableName);
                        map.put("tableChClassName", tableChClassName);
                        map.put("dbInfoId", dbInfoId);
                        list.add(map);
                    }
                }
            }
        }
        return list;
    }

    @Override
    public JSONArray selectFinalDocument(List<Map<String, Object>> tableNameList) throws Exception {

        JSONArray jsonArray = new JSONArray();

        for (Map<String, Object> map : tableNameList) {
            JSONObject jsonObject = new JSONObject();

            String cTableName = (String) map.get("cTableName");
            int dbInfoId = (int) map.get("dbInfoId");
            String tableChClassName = (String) map.get("tableChClassName");

            JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(dbInfoId);
            String sql = "SELECT * FROM " + cTableName + " WHERE " + cTableName + "_state = 3";

            List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);

            if (mapList.size() > 0) {
                jsonObject.put("cTableName", cTableName);
                jsonObject.put("tableChClassName", tableChClassName);
                jsonObject.put("data", mapList);
                jsonArray.add(jsonObject);
            }
        }

        JSONArray jsonArrays = new JSONArray();

        for (int i = 0; jsonArray.size() > i; i++) {
            JSONObject jsonObject = jsonArray.getJSONObject(i);
            List<Map<String, Object>> data = (List<Map<String, Object>>) jsonObject.get("data");
            JSONArray array = JSON.parseArray(JSON.toJSONString(data));
            String cTableName = (String) jsonObject.get("cTableName");

            JdbcTemplate jdbcTemplate1 = dbServer.getJdbcTemplate(2);

            JdbcTemplate jdbcTemplate2 = msFactory.getJdbcTemplate();

            if (cTableName.equals("ApplicationFund")) {

                for (int c = 0; c < array.size(); c++) {
                    JSONObject jsonObject1 = array.getJSONObject(c);

                    JSONObject object = new JSONObject();

                    String code = (String) jsonObject1.get("ApplicationFund_code");// 终审单据编码
                    BigDecimal amount = (BigDecimal) jsonObject1.get("ApplicationFund_xgry_0003");// 应付金额
                    BigDecimal payment = (BigDecimal) jsonObject1.get("ApplicationFund_xgry_0010");// 已付金额
                    BigDecimal balance = (BigDecimal) jsonObject1.get("ApplicationFund_xgry_0016");// 剩余应付金额
                    String payeeNameId = (String) jsonObject1.get("ApplicationFund_xgry_0007");// 收款人id
                    String paymentMode = (String) jsonObject1.get("ApplicationFund_xgry_0005");//支付方式
                    String payBank = (String) jsonObject1.get("ApplicationFund_xgry_0008");// 支付银行
                    String bankCard = (String) jsonObject1.get("ApplicationFund_xgry_0009");// 银行卡号
                    String tableChClassName = (String) jsonObject.get("tableChClassName");// 终审单据类型

                    // 查询收款人姓名   收款人是参照时查人员档案/是登录用户查user_info
                    String sql = "select PersonnelFiles_name from PersonnelFiles where PersonnelFiles_id =  " + payeeNameId;
                    List<Map<String, Object>> mapList = jdbcTemplate1.queryForList(sql);
                    String payeeName = (String) mapList.get(0).get("PersonnelFiles_name");


                    // 过滤付款完成的数据
                    if (balance.compareTo(BigDecimal.ZERO) != 0) {
                        PaymentOrderPage payMentOrderPage = new PaymentOrderPage();
                        payMentOrderPage.setFinalDocumentType(tableChClassName); //终审单据类型
                        payMentOrderPage.setFinalDocumentCoding(code); //终审单据编码
                        payMentOrderPage.setTotalAmountPayable(amount);//应付总金额
                        payMentOrderPage.setTotalAmountPaid(payment);//实付总金额
                        payMentOrderPage.setResidualTotal(balance);//剩余总金额

                        Object o1 = JSONObject.toJSON(payMentOrderPage);
                        JSONArray jsonArray11 = new JSONArray();
                        jsonArray11.add(o1);

                        object.put("payMentOrderPage", jsonArray11);

                        PayMentOrderPageTableBody payMentOrderPageTableBody = new PayMentOrderPageTableBody();
                        payMentOrderPageTableBody.setName(payeeName); // 姓名
                        payMentOrderPageTableBody.setPayMentMethod(paymentMode); // 付款方式
                        payMentOrderPageTableBody.setPayMentBank(payBank); // 支付银行
                        payMentOrderPageTableBody.setBankAccount(bankCard); // 银行账号
                        payMentOrderPageTableBody.setAmountPayable(balance); // 应付金额
                        //payMentOrderPageTableBody.setAmountPaid(payment); // 实付金额


                        Object o = JSONObject.toJSON(payMentOrderPageTableBody);
                        JSONArray jsonArray1 = new JSONArray();
                        jsonArray1.add(o);
                        object.put("payMentOrderPageTableBody", jsonArray1);

                        object.put("tableChClassName", tableChClassName); // 终审单据类型
                        object.put("code", code); // 终审单据编码
                        object.put("amount", amount);//应付总金额
                        object.put("payment", payment);//实付总金额
                        object.put("balance", balance);//剩余总金额
                        object.put("cTableName", cTableName); //终审单据表名

                        jsonArrays.add(object);
                    }
                }

            } else if (cTableName.equals("TransactionFund")) { // 事务备用金

                for (int c = 0; c < array.size(); c++) {
                    JSONObject jsonObject1 = array.getJSONObject(c);

                    JSONObject object = new JSONObject();

                    String code = (String) jsonObject1.get("TransactionFund_code");// 终审单据编码
                    BigDecimal amount = (BigDecimal) jsonObject1.get("TransactionFund_xgry_0003");// 应付金额
                    BigDecimal payment = (BigDecimal) jsonObject1.get("TransactionFund_xgry_0010");// 已付金额
                    BigDecimal balance = (BigDecimal) jsonObject1.get("TransactionFund_xgry_0014");// 剩余应付金额
                    String payeeName = (String) jsonObject1.get("TransactionFund_xgry_0006");// 收款人
                    String paymentMode = (String) jsonObject1.get("TransactionFund_xgry_0004");//支付方式
                    String payBank = (String) jsonObject1.get("TransactionFund_xgry_0007");// 支付银行
                    String bankCard = (String) jsonObject1.get("TransactionFund_xgry_0008");// 银行卡号
                    String tableChClassName = (String) jsonObject.get("tableChClassName");// 终审单据类型

//					// 查询收款人姓名   收款人是参照时查人员档案/是登录用户查user_info
//					String sql = "select PersonnelFiles_name from PersonnelFiles where PersonnelFiles_id =  " + payeeNameId;
//					List<Map<String, Object>> mapList = jdbcTemplate1.queryForList(sql);
//					String payeeName = (String) mapList.get(0).get("PersonnelFiles_name");


                    // 过滤付款完成的数据
                    if (balance.compareTo(BigDecimal.ZERO) != 0) {
                        PaymentOrderPage payMentOrderPage = new PaymentOrderPage();
                        payMentOrderPage.setFinalDocumentType(tableChClassName); //终审单据类型
                        payMentOrderPage.setFinalDocumentCoding(code); //终审单据编码
                        payMentOrderPage.setTotalAmountPayable(amount);//应付总金额
                        payMentOrderPage.setTotalAmountPaid(payment);//实付总金额
                        payMentOrderPage.setResidualTotal(balance);//剩余总金额

                        Object o1 = JSONObject.toJSON(payMentOrderPage);
                        JSONArray jsonArray11 = new JSONArray();
                        jsonArray11.add(o1);

                        object.put("payMentOrderPage", jsonArray11);

                        PayMentOrderPageTableBody payMentOrderPageTableBody = new PayMentOrderPageTableBody();
                        payMentOrderPageTableBody.setName(payeeName); // 姓名
                        payMentOrderPageTableBody.setPayMentMethod(paymentMode); // 付款方式
                        payMentOrderPageTableBody.setPayMentBank(payBank); // 支付银行
                        payMentOrderPageTableBody.setBankAccount(bankCard); // 银行账号
                        payMentOrderPageTableBody.setAmountPayable(balance); // 应付金额
                        //payMentOrderPageTableBody.setAmountPaid(payment); // 实付金额


                        Object o = JSONObject.toJSON(payMentOrderPageTableBody);
                        JSONArray jsonArray1 = new JSONArray();
                        jsonArray1.add(o);
                        object.put("payMentOrderPageTableBody", jsonArray1);

                        object.put("tableChClassName", tableChClassName); // 终审单据类型
                        object.put("code", code); // 终审单据编码
                        object.put("amount", amount);//应付总金额
                        object.put("payment", payment);//实付总金额
                        object.put("balance", balance);//剩余总金额
                        object.put("cTableName", cTableName); //终审单据表名

                        jsonArrays.add(object);
                    }
                }

            } else if (cTableName.equals("TravelExpense")) {
                for (int c = 0; c < array.size(); c++) {
                    JSONObject jsonObject1 = array.getJSONObject(c);

                    JSONObject object = new JSONObject();

                    String code = (String) jsonObject1.get("TravelExpense_code"); // 终审单据编码
                    BigDecimal amount = (BigDecimal) jsonObject1.get("TravelExpense_xgry_0012"); // 应付金额
                    BigDecimal payment = (BigDecimal) jsonObject1.get("TravelExpense_xgry_0015"); // 已付金额
                    BigDecimal balance = (BigDecimal) jsonObject1.get("TravelExpense_xgry_0016"); // 剩余应报金额
                    String payeeNameId = (String) jsonObject1.get("TravelExpense_single_person_id"); // 收款人
                    String payBank = (String) jsonObject1.get("TravelExpense_xgry_0017"); // 支付银行
                    String paymentMode = (String) jsonObject1.get("TravelExpense_xgry_0018"); //支付方式
                    String bankCard = (String) jsonObject1.get("TravelExpense_xgry_0019"); // 银行卡号
                    String tableChClassName = (String) jsonObject.get("tableChClassName"); // 终审单据类型

                    // 查询收款人姓名
                    String sql = "select uName from user_info where userId =  " + payeeNameId;
                    List<Map<String, Object>> mapList = jdbcTemplate2.queryForList(sql);
                    String payeeName = (String) mapList.get(0).get("uName");


                    // 过滤付款完成的数据
                    if (balance.compareTo(BigDecimal.ZERO) != 0) {
                        PaymentOrderPage payMentOrderPage = new PaymentOrderPage();
                        payMentOrderPage.setFinalDocumentType(tableChClassName); // 终审单据类型
                        payMentOrderPage.setFinalDocumentCoding(code); // 终审单据编码
                        payMentOrderPage.setTotalAmountPayable(amount);// 应付总金额
                        payMentOrderPage.setTotalAmountPaid(payment);// 实付总金额
                        payMentOrderPage.setResidualTotal(balance);// 剩余总金额

                        Object o1 = JSONObject.toJSON(payMentOrderPage);
                        JSONArray jsonArray11 = new JSONArray();
                        jsonArray11.add(o1);

                        object.put("payMentOrderPage", jsonArray11);

                        PayMentOrderPageTableBody payMentOrderPageTableBody = new PayMentOrderPageTableBody();
                        payMentOrderPageTableBody.setName(payeeName); // 收款人姓名
                        payMentOrderPageTableBody.setPayMentMethod(paymentMode); // 付款方式
                        payMentOrderPageTableBody.setPayMentBank(payBank); // 支付银行
                        payMentOrderPageTableBody.setBankAccount(bankCard); // 银行账号
                        payMentOrderPageTableBody.setAmountPayable(balance); // 应付金额
//						payMentOrderPageTableBody.setAmountPaid(payment); // 实付金额


                        Object o = JSONObject.toJSON(payMentOrderPageTableBody);
                        JSONArray jsonArray1 = new JSONArray();
                        jsonArray1.add(o);
                        object.put("payMentOrderPageTableBody", jsonArray1);

                        object.put("tableChClassName", tableChClassName);
                        object.put("code", code);
                        object.put("amount", amount);
                        object.put("payment", payment);
                        object.put("balance", balance);
                        object.put("cTableName", cTableName);


                        jsonArrays.add(object);
                    }
                }
            } else if (cTableName.equals("ExpensesReimbursement")) {
                for (int c = 0; c < array.size(); c++) {
                    JSONObject jsonObject1 = array.getJSONObject(c);

                    JSONObject object = new JSONObject();

                    String code = (String) jsonObject1.get("ExpensesReimbursement_code"); // 终审单据编码
                    BigDecimal amount = (BigDecimal) jsonObject1.get("ExpensesReimbursement_xgry_0006"); // 应付金额
                    BigDecimal payment = (BigDecimal) jsonObject1.get("ExpensesReimbursement_xgry_0010"); // 已付金额
                    BigDecimal balance = (BigDecimal) jsonObject1.get("ExpensesReimbursement_xgry_0011"); // 剩余应报金额
                    String payeeNameId = (String) jsonObject1.get("ExpensesReimbursement_single_person_id"); // 收款人
                    String paymentMode = (String) jsonObject1.get("ExpensesReimbursement_xgry_0012"); //支付方式
                    String payBank = (String) jsonObject1.get("ExpensesReimbursement_xgry_0013"); // 支付银行
                    String bankCard = (String) jsonObject1.get("ExpensesReimbursement_xgry_0014"); // 银行卡号
                    String tableChClassName = (String) jsonObject.get("tableChClassName"); // 终审单据类型

                    // 查询收款人姓名
                    String sql = "select uName from user_info where userId =  " + payeeNameId;
                    List<Map<String, Object>> mapList = jdbcTemplate2.queryForList(sql);
                    String payeeName = (String) mapList.get(0).get("uName");


                    // 过滤付款完成的数据
                    if (balance.compareTo(BigDecimal.ZERO) != 0) {
                        PaymentOrderPage payMentOrderPage = new PaymentOrderPage();
                        payMentOrderPage.setFinalDocumentType(tableChClassName); // 终审单据类型
                        payMentOrderPage.setFinalDocumentCoding(code); // 终审单据编码
                        payMentOrderPage.setTotalAmountPayable(amount);// 应付总金额
                        payMentOrderPage.setTotalAmountPaid(payment);// 实付总金额
                        payMentOrderPage.setResidualTotal(balance);// 剩余总金额

                        Object o1 = JSONObject.toJSON(payMentOrderPage);
                        JSONArray jsonArray11 = new JSONArray();
                        jsonArray11.add(o1);

                        object.put("payMentOrderPage", jsonArray11);

                        PayMentOrderPageTableBody payMentOrderPageTableBody = new PayMentOrderPageTableBody();
                        payMentOrderPageTableBody.setName(payeeName); // 收款人姓名
                        payMentOrderPageTableBody.setPayMentMethod(paymentMode); // 付款方式
                        payMentOrderPageTableBody.setPayMentBank(payBank); // 支付银行
                        payMentOrderPageTableBody.setBankAccount(bankCard); // 银行账号
                        payMentOrderPageTableBody.setAmountPayable(balance); // 应付金额
//						payMentOrderPageTableBody.setAmountPaid(payment); // 实付金额


                        Object o = JSONObject.toJSON(payMentOrderPageTableBody);
                        JSONArray jsonArray1 = new JSONArray();
                        jsonArray1.add(o);
                        object.put("payMentOrderPageTableBody", jsonArray1);

                        object.put("tableChClassName", tableChClassName);
                        object.put("code", code);
                        object.put("amount", amount);
                        object.put("payment", payment);
                        object.put("balance", balance);
                        object.put("cTableName", cTableName);


                        jsonArrays.add(object);
                    }
                }
            } else if (cTableName.equals("EntertainmentReimbursement")) {
                for (int c = 0; c < array.size(); c++) {
                    JSONObject jsonObject1 = array.getJSONObject(c);

                    JSONObject object = new JSONObject();

                    String code = (String) jsonObject1.get("EntertainmentReimbursement_code"); // 终审单据编码
                    BigDecimal amount = (BigDecimal) jsonObject1.get("EntertainmentReimbursement_xgry_0006"); // 应付金额
                    BigDecimal payment = (BigDecimal) jsonObject1.get("EntertainmentReimbursement_xgry_0013"); // 已付金额
                    BigDecimal balance = (BigDecimal) jsonObject1.get("EntertainmentReimbursement_xgry_0014"); // 剩余应报金额
                    String payeeNameId = (String) jsonObject1.get("EntertainmentReimbursement_xgry_0012"); // 收款人
                    String paymentMode = (String) jsonObject1.get("EntertainmentReimbursement_xgry_0007"); //支付方式
                    String payBank = (String) jsonObject1.get("EntertainmentReimbursement_xgry_0009"); // 支付银行
                    String bankCard = (String) jsonObject1.get("EntertainmentReimbursement_xgry_0010"); // 银行卡号
                    String tableChClassName = (String) jsonObject.get("tableChClassName"); // 终审单据类型

                    // 查询收款人姓名
                    String sql = "select PersonnelFiles_name from PersonnelFiles where PersonnelFiles_id =  " + payeeNameId;
                    List<Map<String, Object>> mapList = jdbcTemplate1.queryForList(sql);
                    String payeeName = (String) mapList.get(0).get("PersonnelFiles_name");


                    // 过滤付款完成的数据
                    if (balance.compareTo(BigDecimal.ZERO) != 0) {
                        PaymentOrderPage payMentOrderPage = new PaymentOrderPage();
                        payMentOrderPage.setFinalDocumentType(tableChClassName); // 终审单据类型
                        payMentOrderPage.setFinalDocumentCoding(code); // 终审单据编码
                        payMentOrderPage.setTotalAmountPayable(amount);// 应付总金额
                        payMentOrderPage.setTotalAmountPaid(payment);// 实付总金额
                        payMentOrderPage.setResidualTotal(balance);// 剩余总金额

                        Object o1 = JSONObject.toJSON(payMentOrderPage);
                        JSONArray jsonArray11 = new JSONArray();
                        jsonArray11.add(o1);

                        object.put("payMentOrderPage", jsonArray11);

                        PayMentOrderPageTableBody payMentOrderPageTableBody = new PayMentOrderPageTableBody();
                        payMentOrderPageTableBody.setName(payeeName); // 收款人姓名
                        payMentOrderPageTableBody.setPayMentMethod(paymentMode); // 付款方式
                        payMentOrderPageTableBody.setPayMentBank(payBank); // 支付银行
                        payMentOrderPageTableBody.setBankAccount(bankCard); // 银行账号
                        payMentOrderPageTableBody.setAmountPayable(balance); // 应付金额
//						payMentOrderPageTableBody.setAmountPaid(payment); // 实付金额


                        Object o = JSONObject.toJSON(payMentOrderPageTableBody);
                        JSONArray jsonArray1 = new JSONArray();
                        jsonArray1.add(o);
                        object.put("payMentOrderPageTableBody", jsonArray1);

                        object.put("tableChClassName", tableChClassName);
                        object.put("code", code);
                        object.put("amount", amount);
                        object.put("payment", payment);
                        object.put("balance", balance);
                        object.put("cTableName", cTableName);


                        jsonArrays.add(object);
                    }
                }

            } else if (cTableName.equals("Share")) { // 费用分摊
                for (int c = 0; c < array.size(); c++) {
                    JSONObject jsonObject1 = array.getJSONObject(c);

                    JSONObject object = new JSONObject();

                    String code = (String) jsonObject1.get("Share_code"); // 终审单据编码
                    BigDecimal amount = (BigDecimal) jsonObject1.get("Share_xgry_0002"); // 应付金额
                    BigDecimal payment = (BigDecimal) jsonObject1.get("Share_xgry_0012"); // 已付金额
                    BigDecimal balance = (BigDecimal) jsonObject1.get("Share_xgry_0013"); // 剩余应报金额
                    String tableChClassName = (String) jsonObject.get("tableChClassName"); // 终审单据类型

                    String payeeNameId = (String) jsonObject1.get("Share_single_person_id"); // 收款人
                    String paymentMode = (String) jsonObject1.get("Share_xgry_0009"); //支付方式
                    String payBank = (String) jsonObject1.get("Share_xgry_0010"); // 支付银行
                    String bankCard = (String) jsonObject1.get("Share_xgry_0011"); // 银行卡号


                    // 查询收款人姓名
                    String sql = "select uName from user_info where userId =  " + payeeNameId;
                    List<Map<String, Object>> mapList = jdbcTemplate2.queryForList(sql);
                    String payeeName = (String) mapList.get(0).get("uName");


                    // 过滤付款完成的数据
                    if (balance.compareTo(BigDecimal.ZERO) != 0) {
                        PaymentOrderPage payMentOrderPage = new PaymentOrderPage();
                        payMentOrderPage.setFinalDocumentType(tableChClassName); // 终审单据类型
                        payMentOrderPage.setFinalDocumentCoding(code); // 终审单据编码
                        payMentOrderPage.setTotalAmountPayable(amount);// 应付总金额
                        payMentOrderPage.setTotalAmountPaid(payment);// 实付总金额
                        payMentOrderPage.setResidualTotal(balance);// 剩余总金额

                        Object o1 = JSONObject.toJSON(payMentOrderPage);
                        JSONArray jsonArray11 = new JSONArray();
                        jsonArray11.add(o1);

                        object.put("payMentOrderPage", jsonArray11);


                        PayMentOrderPageTableBody payMentOrderPageTableBody = new PayMentOrderPageTableBody();
                        payMentOrderPageTableBody.setName(payeeName); // 收款人姓名
                        payMentOrderPageTableBody.setPayMentMethod(paymentMode); // 付款方式
                        payMentOrderPageTableBody.setPayMentBank(payBank); // 支付银行
                        payMentOrderPageTableBody.setBankAccount(bankCard); // 银行账号
                        payMentOrderPageTableBody.setAmountPayable(balance); // 应付金额


                        Object o = JSONObject.toJSON(payMentOrderPageTableBody);
                        JSONArray jsonArray1 = new JSONArray();
                        jsonArray1.add(o);
                        object.put("payMentOrderPageTableBody", jsonArray1);


                        object.put("tableChClassName", tableChClassName);
                        object.put("code", code);
                        object.put("amount", amount);
                        object.put("payment", payment);
                        object.put("balance", balance);
                        object.put("cTableName", cTableName);

                        jsonArrays.add(object);
                    }
                }

            } else if (cTableName.equals("ServiceFeeReimbursement")) { // 劳务费报销
                for (int c = 0; c < array.size(); c++) {
                    JSONObject jsonObject1 = array.getJSONObject(c);

                    JSONObject object = new JSONObject();

                    String code = (String) jsonObject1.get("ServiceFeeReimbursement_code"); // 终审单据编码
                    String amount1 = (String) jsonObject1.get("ServiceFeeReimbursement_xgry_0003"); // 应付金额
                    BigDecimal payment = (BigDecimal) jsonObject1.get("ServiceFeeReimbursement_xgry_0005"); // 已付金额
                    BigDecimal balance = (BigDecimal) jsonObject1.get("ServiceFeeReimbursement_xgry_0006"); // 剩余应报金额
                    String tableChClassName = (String) jsonObject.get("tableChClassName"); // 终审单据类型

                    BigDecimal amount = new BigDecimal(String.valueOf(amount1));


                    // 过滤付款完成的数据
                    if (balance.compareTo(BigDecimal.ZERO) != 0) {
                        PaymentOrderPage payMentOrderPage = new PaymentOrderPage();
                        payMentOrderPage.setFinalDocumentType(tableChClassName); // 终审单据类型
                        payMentOrderPage.setFinalDocumentCoding(code); // 终审单据编码
                        payMentOrderPage.setTotalAmountPayable(amount);// 应付总金额
                        payMentOrderPage.setTotalAmountPaid(payment);// 实付总金额
                        payMentOrderPage.setResidualTotal(balance);// 剩余总金额

                        Object o1 = JSONObject.toJSON(payMentOrderPage);
                        JSONArray jsonArray11 = new JSONArray();
                        jsonArray11.add(o1);

                        object.put("payMentOrderPage", jsonArray11);

                        // 查询劳务费收款人明细表
                        String sqlFindIdByCode = "SELECT ServiceFeeReimbursement_id FROM ServiceFeeReimbursement WHERE ServiceFeeReimbursement_code = " + code;
                        JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(2);
                        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sqlFindIdByCode);
                        Map<String, Object> map = mapList.get(0);
                        Integer serviceFeeReimbursement_id = (Integer) map.get("ServiceFeeReimbursement_id");

                        String sqlFindDetailsById = "SELECT * FROM DetailsOfServiceFeePayee WHERE ServiceFeeReimbursement_id = " + serviceFeeReimbursement_id;
                        List<Map<String, Object>> detailsMapList = jdbcTemplate.queryForList(sqlFindDetailsById);
                        if (detailsMapList.size() > 0) {
                            JSONArray jsonArray1 = new JSONArray();
                            for (Map<String, Object> detailsMap : detailsMapList) {

                                String payeeName = (String) detailsMap.get("DetailsOfServiceFeePayee_xgry_0001");// 收款人
                                String paymentMode = (String) detailsMap.get("DetailsOfServiceFeePayee_xgry_0002");// 支付方式
                                String payBank = (String) detailsMap.get("DetailsOfServiceFeePayee_xgry_0004");// 持卡银行
                                String bankCard = (String) detailsMap.get("DetailsOfServiceFeePayee_xgry_0005");// 银行卡号
                                BigDecimal detailsOfServiceFeePayee_xgry_0011 = (BigDecimal) detailsMap.get("DetailsOfServiceFeePayee_xgry_0011");// 应收金额
                                //BigDecimal detailsOfServiceFeePayee_xgry_0010 = (BigDecimal) detailsMap.get("DetailsOfServiceFeePayee_xgry_0010");// 实收金额

                                PayMentOrderPageTableBody payMentOrderPageTableBody = new PayMentOrderPageTableBody();
                                payMentOrderPageTableBody.setName(payeeName); // 收款人姓名
                                payMentOrderPageTableBody.setPayMentMethod(paymentMode); // 付款方式
                                payMentOrderPageTableBody.setPayMentBank(payBank); // 支付银行
                                payMentOrderPageTableBody.setBankAccount(bankCard); // 银行账号
                                payMentOrderPageTableBody.setAmountPayable(detailsOfServiceFeePayee_xgry_0011); // 应付金额
//						payMentOrderPageTableBody.setAmountPaid(payment); // 实付金额


                                Object o = JSONObject.toJSON(payMentOrderPageTableBody);
                                jsonArray1.add(o);
                            }
                            object.put("payMentOrderPageTableBody", jsonArray1);
                        }


//					String payeeName = (String) jsonObject1.get("EntertainmentReimbursement_xgry_0012"); // 收款人
//					String paymentMode = (String) jsonObject1.get("EntertainmentReimbursement_xgry_0007"); //支付方式
//					String payBank = (String) jsonObject1.get("EntertainmentReimbursement_xgry_0009"); // 支付银行
//					String bankCard = (String) jsonObject1.get("EntertainmentReimbursement_xgry_0010"); // 银行卡号

                        object.put("tableChClassName", tableChClassName);
                        object.put("code", code);
                        object.put("amount", amount);
                        object.put("payment", payment);
                        object.put("balance", balance);
                        object.put("cTableName", cTableName);

                        jsonArrays.add(object);
                    }
                }
            }
        }
        return jsonArrays;
    }

    @Override
    public JSONArray selectTask(List<Map<String, Object>> tableNameList, HttpSession session) throws Exception {
        UserInfo userInfo = (UserInfo) session.getAttribute(SystemEnum.SESSION_USER.getValue());
        Integer userId = userInfo.getUserId();
        JSONArray jsonArray = new JSONArray();
        List<List<Map<String, Object>>> lists = new ArrayList<>();
        for (Map<String, Object> map : tableNameList) {
            JSONObject jsonObject = new JSONObject();
            String cTableName = (String) map.get("cTableName");
            FormList byCtableName = formListDao.selectOne(new QueryWrapper<FormList>() {{
                this.eq("cTableName", cTableName);
            }});
            int dbInfoId = (int) map.get("dbInfoId");
            String tableChClassName = (String) map.get("tableChClassName");
            JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(dbInfoId);
            String sql = "select * from " + cTableName + " where " + cTableName + "_single_person_id =" + userId + " and " + cTableName + "_state in (1,2,3,4,5,6)";
            List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
            JSONArray jsonArray1 = JSON.parseArray(JSON.toJSONString(mapList));
            if (jsonArray1.size() > 0) {
                for (int i = 0; i < jsonArray1.size(); i++) {
                    JSONObject jsonObject1 = jsonArray1.getJSONObject(i);
                    jsonObject1.put("cTableName", cTableName);
                    jsonObject1.put("formId", byCtableName.getFormListId());
                    jsonObject1.put("tableChClassName", tableChClassName);
                    jsonObject1.put("code", jsonObject1.getString(cTableName + "_code"));
                    String state = jsonObject1.getString(cTableName + "_state");
                    if (state.equals("1")) {
                        jsonObject1.put("state", "已提交");
                    } else if (state.equals("2")) {
                        jsonObject1.put("state", "审批中");
                    } else if (state.equals("3")) {
                        jsonObject1.put("state", "已终审");
                    } else if (state.equals("4")) {
                        jsonObject1.put("state", "驳回");
                    } else if (state.equals("5")) {
                        jsonObject1.put("state", "已中止");
                    } else if (state.equals("6")) {
                        jsonObject1.put("state", "已撤回");
                    }

                    jsonArray.add(jsonObject1);
                }
            }
        }
        return jsonArray;
    }

    @Override
    public JSONArray selectTaskTwo(List<Map<String, Object>> tableNameList, HttpSession session, JSONObject jsonStr) throws Exception {
        UserInfo userInfo = (UserInfo) session.getAttribute(SystemEnum.SESSION_USER.getValue());
        Integer userId = userInfo.getUserId();
        JSONArray jsonArray = new JSONArray();
        String tabelName = jsonStr.getString("colVal");
        List<List<Map<String, Object>>> lists = new ArrayList<>();
        for (Map<String, Object> map : tableNameList) {
            JSONObject jsonObject = new JSONObject();
            String cTableName = (String) map.get("cTableName");
            if (cTableName.equals(tabelName) || cTableName == tabelName) {
                FormList byCtableName = formListDao.selectOne(new QueryWrapper<FormList>() {{
                    this.eq("cTableName", cTableName);
                }});
                int dbInfoId = (int) map.get("dbInfoId");
                String tableChClassName = (String) map.get("tableChClassName");
                JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(dbInfoId);
                String sql = "select * from " + cTableName + " where " + cTableName + "_code = " + jsonStr.getString("keyVal") + " and " + cTableName + "_single_person_id =" + userId + " and " + cTableName + "_state in (1,2,3,4,5,6)";
                List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
                JSONArray jsonArray1 = JSON.parseArray(JSON.toJSONString(mapList));
                if (jsonArray1.size() > 0) {
                    for (int i = 0; i < jsonArray1.size(); i++) {
                        JSONObject jsonObject1 = jsonArray1.getJSONObject(i);
                        jsonObject1.put("cTableName", cTableName);
                        jsonObject1.put("formId", byCtableName.getFormListId());
                        jsonObject1.put("tableChClassName", tableChClassName);
                        jsonObject1.put("code", jsonObject1.getString(cTableName + "_code"));
                        String state = jsonObject1.getString(cTableName + "_state");
                        if (state.equals("1")) {
                            jsonObject1.put("state", "已提交");
                        } else if (state.equals("2")) {
                            jsonObject1.put("state", "审批中");
                        } else if (state.equals("3")) {
                            jsonObject1.put("state", "已终审");
                        } else if (state.equals("4")) {
                            jsonObject1.put("state", "驳回");
                        } else if (state.equals("5")) {
                            jsonObject1.put("state", "已中止");
                        } else if (state.equals("6")) {
                            jsonObject1.put("state", "已撤回");
                        }

                        jsonArray.add(jsonObject1);
                    }
                }
            }
        }
        return jsonArray;
    }

    @Override
    public int count(int formListId) throws Exception {
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletRequest request = attributes.getRequest();
        HttpSession session = request.getSession();
        UserInfo userInfo = (UserInfo) session.getAttribute(SystemEnum.SESSION_USER.getValue());
        JSONArray jsonArray = new JSONArray();
        JSONObject retuJson = new JSONObject();
        JdbcTemplate jdbcTemplate = msFactory.getJdbcTemplate();
        StringBuffer stringBuffer = new StringBuffer();
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList("select * from form_list where formListId=" + formListId);
        JSONArray formList = JSONArray.parseArray(JSON.toJSONString(mapList));
        JSONObject jsonObject = formList.getJSONObject(0);
        String mTable = jsonObject.getString("cTableName");
        //	System.out.println("mTable:" + mTable);
        List<Map<String, Object>> tableList = jdbcTemplate.queryForList("select * from table_class where tableClassName='" + mTable + "'");
        JSONArray cols = JSON.parseArray(JSON.toJSONString(tableList));
        JSONObject jdbcJSON = cols.getJSONObject(0);
        Integer dbInfoId = jdbcJSON.getInteger("dbInfoId");
        JdbcTemplate dbServerJdbcTemplate = dbServer.getJdbcTemplate(jdbcJSON.getInteger("dbInfoId"));
        int mTableId = jdbcJSON.getInteger("tableClassId");
        List<Map<String, Object>> sTableList = jdbcTemplate.queryForList("select * from table_class where mTableId=" + mTableId);
        List<CreateTable> createTables = createTableDao.selectList(new QueryWrapper<CreateTable>() {{
            this.eq("tableName", mTable);
        }});
        ;
        TableClass tableClass = tableClassDao.selectOne(new QueryWrapper<TableClass>() {{
            this.eq("tableClassName", mTable);
        }});
        ;
        stringBuffer.append("select  COUNT(1) FROM " + tableClass.getTableClassName());
        List<Map<String, Object>> formDataList = dbServerJdbcTemplate.queryForList(stringBuffer.toString());
        Object o = formDataList.get(0).get("");
        return (int) o;
    }

//		/**
//		 * 查询所有付款单据的formlistId、tableName
//		 * @param tableNameList
//		 * @return
//		 * @throws Exception
//		 */
//		@Override
//		public List<Map<String,Object>> selctDocumentPayment(List<Map<String, Object>> tableNameList) throws Exception {
//
//			List<Map<String,Object>> modelList = new ArrayList<>();
//
//			// 查询model表中的  模型名称
//			String findAllModelSql = "select id,modelName from model";
//			JdbcTemplate jdbcTemplate = msFactory.getJdbcTemplate();
//			List<Map<String, Object>> mapList = jdbcTemplate.queryForList(findAllModelSql);
//
//			// 找出 model表 中模型名称 包含 终审单据名称 的数据 （需要用到终审单据对应的付款单据）
//			if (mapList.size() > 0 ) {
//				for (Map<String, Object> map1 : mapList) {
//					String modelName = (String) map1.get("modelName");
//
//					for (Map<String, Object> map2 : tableNameList) {
//						String tableChClassName = (String) map2.get("tableChClassName");
//						if (modelName.indexOf(tableChClassName) == 0 ) {
//							modelList.add(map1);
//						}
//					}
//				}
//			}
//
//			//	通过 modelId 查询 from_list表 数据
//			StringBuffer stringBuffer = new StringBuffer();
//			stringBuffer.append("select * from form_list where modelId in (");
//			for (Map<String, Object> map : modelList) {
//				int id = (int) map.get("id");
//				stringBuffer.append(id + ",");
//			}
//			stringBuffer.delete(stringBuffer.toString().lastIndexOf(","),stringBuffer.length());
//			stringBuffer.append(")");
//			List<Map<String, Object>> formListMap = jdbcTemplate.queryForList(stringBuffer.toString());
//
//			// jsonArray中存放终审单据和终审单据对应付款单据的formListId
//			JSONArray jsonArray = new JSONArray();
//			// form_list 的数据包含的 终审单据
//			for (Map<String, Object> map : formListMap) {
//				String formName = (String) map.get("formName");
//				for (Map<String, Object> objectMap : tableNameList) {
//					String tableChClassName = (String) objectMap.get("tableChClassName");
//					if (formName.indexOf(tableChClassName) == 0 ) {
//						JSONObject jsonObject = new JSONObject();
//						jsonObject.put("formListId",map.get("formListId"));
//						jsonObject.put("cTableName",map.get("cTableName"));
//						jsonObject.put("formName",map.get("formName"));
//						jsonArray.add(jsonObject);
//					}
//				}
//			}
//
//			for (int i = 0; jsonArray.size() > i; i++) {
//				JSONObject jsonObject = jsonArray.getJSONObject(i);
//				String fromName = (String) jsonObject.get("formName");
//				//String cTableName = (String) jsonObject.get("cTableName");
//				for (Map<String, Object> map : tableNameList) {
//					String tableChClassName = (String) map.get("tableChClassName");
//					//String cTableName1 = (String) map.get("cTableName");
//					if (fromName.indexOf(tableChClassName) == 0) {
//						String cTableName = (String) jsonObject.get("cTableName");
//						String cTableName1 = (String) map.get("cTableName");
//						if (!cTableName.equals(cTableName1)) {
//							int formListId = (int) jsonObject.get("formListId");
//							map.put("formListId",formListId);
//						}
//					}
//				}
//			}
//			return tableNameList;
//		}
//
//		@Override
//		public JSONArray findFinalDocument(List<Map<String, Object>> list) throws Exception {
//			JSONArray jsonArray = new JSONArray();
//			for (Map<String, Object> map : list) {
//				String cTableName = (String) map.get("cTableName");
//				int dbInfoId = (int) map.get("dbInfoId");
//				String tableChClassName = (String) map.get("tableChClassName");
//
//				JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(dbInfoId);
//				String sql = "SELECT * FROM " + cTableName + " WHERE " + cTableName + "_state = 3";
//
//				if (map.containsKey("formListId")) {
//					int formListId = (int) map.get("formListId");
//					List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
//					if (mapList.size() > 0) {
//						for (Map<String, Object> objectMap : mapList) {
//							JSONObject jsonObject = new JSONObject();
//							jsonObject.put("cTableName",cTableName);
//							jsonObject.put("tableChClassName",tableChClassName);
//							jsonObject.put("formListId",84);
//							jsonObject.put("data",objectMap);
//							jsonArray.add(jsonObject);
//						}
//					}
//				} else {
//					List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
//					if (mapList.size() > 0) {
//						for (Map<String, Object> objectMap : mapList) {
//							JSONObject jsonObject = new JSONObject();
//							jsonObject.put("cTableName",cTableName);
//							jsonObject.put("tableChClassName",tableChClassName);
//							jsonObject.put("formListId","84");
//							jsonObject.put("data",objectMap);
//							jsonArray.add(jsonObject);
//						}
//					}
//				}
//			}
//			return jsonArray;
//		}

    public String deleteTable(String tableName) {

        String sql = "drop table " + tableName;

        return sql;
    }

//	public String deleteField(String tableName,String field) {
//	String sql = "ALTER TABLE " + tableName + " DROP COLUMN " + field;
//
//		return sql;
//	}

    public String deleteView(String viewName) {
        String sql = "drop view " + viewName;
        return sql;
    }

    //创建视图
    public String createView(String viewName, List<String> sList, String tableName) {
        StringBuffer sb = new StringBuffer();
        sb.append("create view " + viewName + " AS select ");
        for (int i = 0; i < sList.size(); i++) {
            sb.append(sList.get(i) + ",");
        }
        sb.delete(sb.toString().lastIndexOf(","), sb.length());
        sb.append(" from " + tableName);
        return sb.toString();
    }

    //查询视图的sql
    public String viewSql(String viewName, List<String> sList) {

        StringBuffer sb = new StringBuffer();
        sb.append("select ");
        for (int i = 0; i < sList.size(); i++) {
            sb.append(sList.get(i) + ",");
        }
        sb.delete(sb.toString().lastIndexOf(","), sb.length());
        sb.append(" from " + viewName);

        return sb.toString();
    }

    public String tableViewSql(String tableViewName, List<String> sList) {
        StringBuffer sb = new StringBuffer();

        sb.append("select ");
        for (int i = 0; i < sList.size(); i++) {
            sb.append(sList.get(i) + ", ");
        }
        sb.delete(sb.toString().lastIndexOf(","), sb.length());
        sb.append(" from " + tableViewName);
        return sb.toString();
    }

    public String formViewSql(String tableViewName, Map<String, String> mList, List<String> sList) {
        StringBuffer sb = new StringBuffer();
        sb.append("select ");
//		for(int i = 0; i < mList.size(); i ++) {
        Map<String, String> maps = mList;
        for (Entry<String, String> map : maps.entrySet()) {
            sb.append(tableViewName + "." + map.getKey() + ",");
        }
//		}
        sb.delete(sb.toString().lastIndexOf(","), sb.length());
        sb.append(" from " + tableViewName);


        return sb.toString();
    }

    public String createFormViewSql(String tableViewName, Map<String, String> mList, List<String> sList) {
        StringBuffer sb = new StringBuffer();
        sb.append("create view " + tableViewName + " as ");
        sb.append(" select ");

//		for(int i = 0; i < mList.size(); i ++) {
        Map<String, String> maps = mList;
        for (Entry<String, String> map : maps.entrySet()) {
            sb.append(map.getValue() + "." + map.getKey() + ",");
        }
//		}

        sb.delete(sb.toString().lastIndexOf(","), sb.length());
        String[] tableview = tableViewName.split("_");
        sb.append(" from " + tableview[0]);
        if (sList.size() > 1) {
            for (int j = 1; j < sList.size(); j++) {
                sb.append(" left join " + sList.get(j) + " on " +
                        sList.get(0) + ".id=" + sList.get(j) + ".mTableId");
            }
        }
        return sb.toString();
    }

    public String findConstraint(String tableName, String field) {
        // TODO Auto-generated method stub
        StringBuffer sBuffer = new StringBuffer();
        sBuffer.append("select b.name from sysobjects b join syscolumns a on b.id = a.cdefault where a.id = object_id('");
        sBuffer.append(tableName + "')and a.name = '" + field + "'");

        return sBuffer.toString();
    }

    public String deleteConstraint(String tableName, String constraintName) {
        // TODO Auto-generated method stub
        String sql = "ALTER TABLE " + tableName + " DROP " + constraintName;
        return sql;
    }

    public JSONArray findByParentLevel(String tableName, String parentLevel) throws Exception {
        List<CreateTable> createTables = createTableDao.selectList(new QueryWrapper<CreateTable>() {{
            this.eq("tableName", tableName);
        }});
        ;
        List<TableClass> tableClasses = tableClassDao.selectList(new QueryWrapper<TableClass>() {{
            this.eq("tableClassName", (tableName));
        }});
        JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(tableClasses.get(0).getDbInfoId());
        StringBuffer sBuffer = new StringBuffer();
        sBuffer.append("select ");
//		String sql = "select "+
        for (int i = 0; i < createTables.size(); i++) {
//			sBuffer.append(createTables.get(i).getField() + " as " + tableName + "_" + createTables.get(i).getField() + ",");
            sBuffer.append(createTables.get(i).getField() + " as " + createTables.get(i).getField() + ",");
        }
        sBuffer.append(tableName + "_id as " + tableName + "_id,");
        sBuffer.append(tableName + "_name as name,");
        sBuffer.delete(sBuffer.toString().lastIndexOf(","), sBuffer.length());
        sBuffer.append(" from " + tableName + " " + tableName + " where parent_level=" + parentLevel);
        sBuffer.append(" or " + tableName + "_id=" + parentLevel + "");
        //		System.out.println("svvvvvvvvvvvvvvv"+sBuffer);
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sBuffer.toString());
        JSONArray reJsonArray = JSON.parseArray(JSON.toJSONString(mapList));
        return reJsonArray;
    }

    //删除表关联关系
    public String deleteFileForeignKey(String tableName, String keyName) {
        String sql = "alter table " + tableName + " drop constraint " + keyName;
        return sql;
    }

    public String findByRef(String fieldName, String tableName, String key, String value) {
        String sql = "select " + fieldName + " from " + tableName + " where " + key + "=" + value;
        return sql;
    }


    public String findByDb(String tableName, Map<String, String> maps, List<String> sList) {

        StringBuffer sBuffer = new StringBuffer();

        sBuffer.append("select ");

        for (Entry<String, String> map : maps.entrySet()) {
            sBuffer.append(map.getKey() + " as " + map.getValue() + ",");
        }
        sBuffer.delete(sBuffer.toString().lastIndexOf(","), sBuffer.length());
        sBuffer.append(" from " + tableName);
        if (sList.size() > 0) {
            for (int i = 0; i < sList.size(); i++) {
                sBuffer.append(" left join " + sList.get(i) + " on " + tableName + ".id=" + sList.get(i) + ".mTableId");

            }
        }
        return sBuffer.toString();
    }


    public String findByArchives(String tbleName, String tableFieldKey, String tableFileValue) {
        StringBuffer sBuffer = new StringBuffer();
        sBuffer.append("select * from ");
        sBuffer.append(" where " + tableFieldKey + " = '" + tableFileValue + "'");
        return sBuffer.toString();
    }

    public void deleteField(String tablename, String field) throws Exception {
        JdbcTemplate jdbcTemplate = msFactory.getJdbcTemplate();
        jdbcTemplate.update("delete from create_table where field='" + field + "'");
        List<Map<String, Object>> map = jdbcTemplate.queryForList("select * from table_class where tableClassName='" + tablename + "'");
        JSONArray jsonArray1 = JSON.parseArray(JSON.toJSONString(map));
        JSONObject jsonObject2 = jsonArray1.getJSONObject(0);
        JdbcTemplate jdbcTemplate1 = dbServer.getJdbcTemplate(jsonObject2.getInteger("dbInfoId"));
        jdbcTemplate1.update("ALTER TABLE " + tablename + " DROP column " + field + "");

    }

    @Override
    public void insertImprest(JSONArray jsonArray) throws Exception {
        JSONObject jsonObject = jsonArray.getJSONObject(0);
        JSONObject jsonObject1 = jsonObject.getJSONObject("obj");
        String tablename = jsonObject.getString("cTableName");
        JdbcTemplate jdbcTemplate = msFactory.getJdbcTemplate();
        List<Map<String, Object>> olist = jdbcTemplate.queryForList("SELECT  * from table_class where tableClassName='" + tablename + "'");
        JSONArray jsonArray1 = JSON.parseArray(JSON.toJSONString(olist));
        JSONObject jsonObject2 = jsonArray1.getJSONObject(0);
        JdbcTemplate jdbc = dbServer.getJdbcTemplate(jsonObject2.getInteger("dbInfoId"));
        jdbc.update("update ApplicationFund set ApplicationFund_xgry_0010='" + jsonObject1.getString("srpayment_xgry_0005") + "' where ApplicationFund_id=" + jsonObject1.getString("srpayment_xgry_0001") + "");

    }


    @Override
    public void insertuser(JSONArray jsonArray) throws Exception {
        JSONObject jsonObject = jsonArray.getJSONObject(0);
        String tablename = jsonObject.getString("cTableName");
        JSONObject jsonObject1 = jsonObject.getJSONObject("obj");
        //		System.out.println("jsomObject1"+jsonObject1);
        String uname = jsonObject1.getString("PersonnelFiles_name");
        String name = jsonObject1.getString("PersonnelFiles_OperatorName");
        String uid = jsonObject1.getString("PersonnelFiles_OperatorCode");
        String email = jsonObject1.getString("PersonnelFiles_Email");
        String b = "1";
        String passwod = MD5Util.MD5(b);
        String code = jsonObject1.getString("PersonnelFiles_code");
        JdbcTemplate jdbcTemplate = msFactory.getJdbcTemplate();
        List<Map<String, Object>> map = jdbcTemplate.queryForList("select * from  table_class where tableClassName='" + tablename + "'");
        JSONArray jsonArray1 = JSON.parseArray(JSON.toJSONString(map));
        JSONObject jsonObject2 = jsonArray1.getJSONObject(0);
        JdbcTemplate jdbcTemplate1 = dbServer.getJdbcTemplate(jsonObject2.getInteger("dbInfoId"));

        List<Map<String, Object>> maps = jdbcTemplate1.queryForList("select * from PersonnelFiles where PersonnelFiles_OperatorCode='" + uid + "'");
        JSONArray jsonArray2 = JSON.parseArray(JSON.toJSONString(maps));
        JSONObject jsonObject3 = jsonArray2.getJSONObject(0);
        String personid = jsonObject3.getString("PersonnelFiles_id");
        List<Map<String, Object>> maps1 = jdbcTemplate.queryForList("select * from  user_info where personnelId=" + personid + "");
        if (maps1.size() > 0) {
            jdbcTemplate.update("update user_info set uName='" + uname + "',userEmail='" + email + "',userCode='" + name + "',userName='" + uid + "' where personnelId=" + personid + "");
            jdbcTemplate.update("update userinfo_jurisdiction set userName='" + uname + "',userNumber='" + uid + "',userjurisdiction='" + uid + "'");
        }
        if (!uid.equals("") && !name.equals("") && maps1.size() <= 0) {
            jdbcTemplate.update("insert into user_info(uName,personnelId,userEmail,userState,password,userCode,userName) values ('" + uname + "'," + personid + ",'" + email + "',1,'" + passwod + "','" + name + "','" + uid + "')");
            jdbcTemplate.update("insert into userinfo_jurisdiction(userName,userNumber,userjurisdiction) values ('" + uname + "','" + uid + "','" + uid + "')");

        }

    }

}

